Load from External Kafka

After you have defined a graph schema, you can create a loading job, specify your data sources, and run the job to load data.

The steps for loading from local files, cloud storage, or any other supported sources are similar. We will call out whether a particular step is common for all loading or specific to a data source or loading mode.

Example Schema

Example schema taken from LDBC_SNB
//Vertex Types:
CREATE VERTEX Person(PRIMARY_ID id UINT, firstName STRING, lastName STRING,
  gender STRING, birthday DATETIME, creationDate DATETIME, locationIP STRING,
  browserUsed STRING, speaks SET<STRING>, email SET<STRING>)
  WITH STATS="OUTDEGREE_BY_EDGETYPE", PRIMARY_ID_AS_ATTRIBUTE="true"
CREATE VERTEX Comment(PRIMARY_ID id UINT, creationDate DATETIME,
  locationIP STRING, browserUsed STRING, content STRING, length UINT)
  WITH STATS="OUTDEGREE_BY_EDGETYPE", PRIMARY_ID_AS_ATTRIBUTE="true"
//Edge Types:
CREATE DIRECTED EDGE HAS_CREATOR(FROM Comment, TO Person)
  WITH REVERSE_EDGE="HAS_CREATOR_REVERSE"

Create Data Source Object

A data source object provides a standard interface for all supported data source types, so that loading jobs can be written without regard for the data source.

When you create the object, you specify its details (type, access credentials, etc.) in the form of a JSON object. The JSON object can either be read in from a file or provided inline.

Inline mode is required when creating data sources for TigerGraph Cloud instances.

In the following example, we create a data source named s1, and read its configuration information from a file called ds_config.json.

USE GRAPH ldbc_snb
CREATE DATA_SOURCE s1 = "ds_config.json" FOR GRAPH ldbc_snb

Older versions of TigerGraph required a keyword after DATA_SOURCE such as STREAM or KAFKA.
Inline JSON data format when creating a data source
CREATE DATA_SOURCE s1 = "{
  type: <type>,
  key: <value>
}" FOR GRAPH ldbc_snb

String Literals

String literals can be represented according to the following options:

  • Enclosed with double quote ".

  • Enclosed with triple double quotes """.

  • Enclosed with triple single quotes '''.

In the case of JSON that does not contain a colon : or a comma , the double quotes " can be omitted.

Alternate quote syntax for inline JSON data
CREATE DATA_SOURCE s1 = """{
  "type": "<type>",
  "key": "<value>"
}""" FOR GRAPH ldbc_snb

Either a period . or _ can be used for separation in the specified key name. Example: first.second or first_second.

Configure the Kafka source

To configure the data source object for Kafka, the minimum requirement is the address of the external source Kafka cluster:

Data source configuration for an external Kafka source
{
  "type": "kafka",
  "bootstrap.servers": "{broker-addresses}"
}

Additional Configurations

These configurations are consistent with the Java Kafka consumer client settings.

To customize the consumer for authentication or advanced configurations, refer to the official Kafka documentation: Kafka Consumer Configurations.

Examples for SSL Encryption and Authentication

Each configuration must include the type and bootstrap.servers properties to specify the Kafka data source type and broker addresses.

SSL Configuration

To configure SSL for secure communication with the Kafka cluster, include the following properties:

{
  "type": "kafka",
  "bootstrap.servers": "{broker-addresses}",
  "security.protocol": "SSL",
  "ssl.truststore.location": "{truststore-location}",
  "ssl.truststore.password": "{truststore-password}",
  "ssl.keystore.location": "{keystore-location}",
  "ssl.keystore.password": "{keystore-password}",
  "ssl.key.password": "{key-password}"
}
SASL_PLAINTEXT Configuration

For SASL authentication over plaintext, use the following configuration:

{
  "type": "kafka",
  "bootstrap.servers": "{broker-addresses}",
  "security.protocol": "SASL_PLAINTEXT",
  "sasl.mechanism": "PLAIN",
  "sasl.jaas.config": "org.apache.kafka.common.security.plain.PlainLoginModule required username=\"{username}\" password=\"{password}\";"
}
SASL_SSL Configuration

For SASL authentication over SSL, use the following configuration:

{
  "type": "kafka",
  "bootstrap.servers": "{broker-addresses}",
  "security.protocol": "SASL_SSL",
  "sasl.mechanism": "PLAIN",
  "sasl.jaas.config": "org.apache.kafka.common.security.plain.PlainLoginModule required username=\"{username}\" password=\"{password}\";",
  "ssl.truststore.location": "{truststore-location}",
  "ssl.truststore.password": "{truststore-password}"
}
Kerberos (SASL_SSL + GSSAPI) Configuration

For Kerberos authentication over TLS, use the GSSAPI SASL mechanism with the SASL_SSL security protocol.

Before creating the data source, complete the setup described in Configure Kerberos Authentication.

{
  "type": "kafka",
  "bootstrap.servers": "{broker-addresses}",
  "security.protocol": "SASL_SSL",
  "sasl.mechanism": "GSSAPI",
  "sasl.kerberos.service.name": "{kerberos-service-name}",
  "sasl.jaas.config": "com.sun.security.auth.module.Krb5LoginModule required useKeyTab=true storeKey=true doNotPrompt=true keyTab=\"{keytab-path}\" principal=\"{client-     principal}\";",
  "ssl.truststore.type": "PEM",
  "ssl.truststore.certificates": "{ca-certificate-pem}"
}
Amazon MSK Configuration

For Amazon MSK with IAM authentication, use the following configuration:

{
  "type": "kafka",
  "bootstrap.servers": "{broker-addresses}",
  "security.protocol": "SASL_SSL",
  "sasl.mechanism": "AWS_MSK_IAM",
  "sasl.jaas.config": "software.amazon.msk.auth.iam.IAMLoginModule required;",
  "sasl.client.callback.handler.class": "software.amazon.msk.auth.iam.IAMClientCallbackHandler",
  "ssl.truststore.location": "{truststore-location}",
  "ssl.truststore.password": "{truststore-password}"
}
Google Cloud Kafka Configuration

For Kafka on Google Cloud with SASL authentication, use the following configuration:

{
  "type": "kafka",
  "bootstrap.servers": "{broker-addresses}",
  "security.protocol": "SASL_SSL",
  "sasl.mechanism": "OAUTHBEARER",
  "sasl.jaas.config": "org.apache.kafka.common.security.oauthbearer.OAuthBearerLoginModule required;",
  "sasl.login.callback.handler.class": "com.google.cloud.hosted.kafka.auth.GcpLoginCallbackHandler",
  "ssl.truststore.location": "{truststore-location}",
  "ssl.truststore.password": "{truststore-password}"
}

Kerberos Authentication (SASL_SSL + GSSAPI)

The Kafka connector supports Kerberos authentication to Kafka brokers over TLS using the SASL_SSL security protocol and the GSSAPI SASL mechanism. TigerGraph authenticates as a Kafka consumer using its Kerberos client principal and the corresponding keytab.

A keytab for the TigerGraph consumer principal is required on every node running KAFKACONN. The Kafka Java client cannot authenticate using a password, so Kerberos authentication requires a keytab. TLS CA certificates can be provided inline in the Kafka data source JSON, and Kerberos realm and KDC settings can be configured through JVM options. Therefore, the keytab is the only additional file that must be distributed to the connector nodes when JVM options are sufficient for the Kerberos configuration.

Use the krb5.conf option only when you need additional Kerberos configuration, such as a non-standard KDC port or a TCP-only network.

This section describes authentication between TigerGraph and Kafka. It is separate from single sign-on (SSO) with Kerberos, which is used for browser-based authentication to TigerGraph.
Prerequisites

Before configuring Kerberos authentication, ensure that the following requirements are met:

  • Every node running the Kafka connector can reach the Kafka brokers on their configured SASL_SSL ports and the Kerberos Key Distribution Center (KDC) on its configured port.

  • The broker hostnames resolve to fully qualified domain names (FQDNs) that match the broker’s Kerberos service principals and the hostnames in its TLS certificates.

  • The clocks on the TigerGraph nodes, KDC, and Kafka brokers are synchronized using Network Time Protocol (NTP).

  • The Kerberos client principal used by TigerGraph as its Kafka consumer and the corresponding keytab must be provisioned for TigerGraph. The keytab must be available on every node running KAFKACONN.

  • The client principal has the Kafka topic permissions required for the intended operation, including Read and Describe permissions for the topics being consumed.

  • The CA certificate required to validate the brokers' TLS certificates is available if it is not already trusted by the connector’s default Java trust configuration.

Choose the Correct Keytab

Use the keytab associated with the Kerberos principal that TigerGraph uses as its Kafka consumer, for example, tg-loader@EXAMPLE.COM. This principal identifies TigerGraph when it authenticates to Kafka.

The keytab must be provisioned for TigerGraph and placed on the TigerGraph servers by the administrator responsible for those servers. Do not use a keytab belonging to a Kafka broker or assume that the downstream application team’s keytab is appropriate.

For example, do not use a broker’s service keytab associated with kafka/broker1.example.com@EXAMPLE.COM. That keytab identifies the broker, not the TigerGraph consumer.

The client keytab must be readable by the Kafka connector on every node running KAFKACONN. Use the same absolute path on every node.

Configure the Kerberos Client

If the connector can already access a valid /etc/krb5.conf file for the required Kerberos realm, you may not need to configure the realm and KDC through JVM properties. Otherwise, use one of the following options.

Both options set KafkaConnect.BasicConfig.Env, replacing its current value. Before changing this setting, check the existing configuration and preserve any other environment variables or JVM options required by your deployment. 
gadmin config get KafkaConnect.BasicConfig.Env

Keep the --add-opens=java.base/java.nio=ALL-UNNAMED option in the KAFKA_OPTS value shown in the examples.

Option A: Configure the Realm and KDC Through JVM Properties

Use this option when the KDC uses the standard Kerberos port, 88, and the default Java Kerberos configuration is suitable for your environment. Ensure that every node running KAFKACONN can reach the KDC over UDP port 88.

Replace EXAMPLE.COM and the KDC hostnames with the values for your environment. If multiple KDCs are configured, separate their hostnames with colons.

The java.security.krb5.kdc value cannot include a port number. Because colons separate KDC hostnames, a value such as kdc1.example.com:8888 is not interpreted as a host and port. If the KDC uses a port other than 88, use Option B instead. 
gadmin config set KafkaConnect.BasicConfig.Env "KAFKA_OPTS=--add-opens=java.base/java.nio=ALL-UNNAMED -Djava.security.krb5.realm=EXAMPLE.COM -Djava.security.krb5.kdc=kdc1.example.com:kdc2.example.com"
gadmin config apply -y
gadmin restart KAFKACONN -y

Option B: Configure a krb5.conf File

Use this option when additional Kerberos configuration is required, such as a non-standard KDC port or a TCP-only network.

  1. Create a krb5.conf file with the realm and KDC configuration. For example:

[libdefaults]
    default_realm = EXAMPLE.COM
    udp_preference_limit = 1

[realms]
    EXAMPLE.COM = {
        kdc = kdc1.example.com:8888
    }

Replace EXAMPLE.COM, kdc1.example.com, and 8888 with the values for your environment.

  1. Copy the configuration file to the same absolute path on every node running KAFKACONN. Run the following commands from a node where the source file is available at /home/tigergraph/secrets/krb5.conf:

grun KAFKACONN "mkdir -p /home/tigergraph/secrets"
gscp KAFKACONN /home/tigergraph/secrets/krb5.conf /home/tigergraph/secrets/krb5.conf

  1. Configure the connector JVM to use the file:

gadmin config set KafkaConnect.BasicConfig.Env "KAFKA_OPTS=--add-opens=java.base/java.nio=ALL-UNNAMED -Djava.security.krb5.conf=/home/tigergraph/secrets/krb5.conf"
gadmin config apply -y
gadmin restart KAFKACONN -y

Ensure that the configured file path is accessible to the connector process on every applicable node.

Distribute the Keytab

Copy the client keytab to every node running KAFKACONN. The following example assumes that the source keytab exists at /home/tigergraph/secrets/tg-loader.keytab on the node where you run the commands.

grun KAFKACONN "mkdir -p /home/tigergraph/secrets"
gscp KAFKACONN /home/tigergraph/secrets/tg-loader.keytab /home/tigergraph/secrets/tg-loader.keytab
grun KAFKACONN "chmod 600 /home/tigergraph/secrets/tg-loader.keytab"

Ensure that:

  • The keytab exists at the specified source path before running the copy command.

  • The keytab is available at the same absolute path on every connector node.

  • The account running the connector can read the keytab.

  • The keytab is protected against unauthorized access.

A shared filesystem mounted at the same path on every connector node can also be used if it meets the deployment’s access and security requirements.

If you add connector nodes later, make the keytab available on the new nodes. If you use Option B, also distribute the krb5.conf file.

Configure the Kafka Data Source

Create a JSON file containing the Kafka connection properties. The following example illustrates the Kerberos settings and the PEM-encoded CA certificate configuration supported by the Kafka connector.

{
  "type": "kafka",
  "bootstrap.servers": "broker1.example.com:9093,broker2.example.com:9093",
  "security.protocol": "SASL_SSL",
  "sasl.mechanism": "GSSAPI",
  "sasl.kerberos.service.name": "kafka",
  "sasl.jaas.config": "com.sun.security.auth.module.Krb5LoginModule required useKeyTab=true storeKey=true doNotPrompt=true keyTab=\"/home/tigergraph/secrets/tg-loader.keytab\" principal=\"tg-loader@EXAMPLE.COM\";",
  "ssl.truststore.type": "PEM",
  "ssl.truststore.certificates": "-----BEGIN CERTIFICATE-----\nREPLACE_WITH_BASE64_ENCODED_CA_CERTIFICATE\n-----END CERTIFICATE-----\n"
}

Replace the broker addresses, client principal, keytab path, and certificate with the values for your environment. The certificate contents shown are illustrative; replace them with the complete PEM-encoded CA certificate required to validate the broker certificate chain.

If the Kafka broker requires client-certificate authentication (ssl.client.auth=required), configure ssl.keystore.type, ssl.keystore.certificate.chain, and ssl.keystore.key in the same top-level JSON object. The private key must be in PKCS#8 format.

{
  "ssl.keystore.type": "PEM",
  "ssl.keystore.certificate.chain": "-----BEGIN CERTIFICATE-----\nREPLACE_WITH_CLIENT_CERTIFICATE\n-----END CERTIFICATE-----\n",
  "ssl.keystore.key": "-----BEGIN PRIVATE KEY-----\nREPLACE_WITH_PKCS8_PRIVATE_KEY\n-----END PRIVATE KEY-----\n"
}

These properties must be added to the same top-level JSON object as the Kafka connection properties. Do not use them unless the Kafka broker requires client-certificate authentication.

GraphStudio-uploaded files are blocked by GSQL.FileInputPolicy. Do not upload the keytab through GraphStudio. Provision the keytab on the TigerGraph servers and ensure that the Kafka connector can read it on every node running KAFKACONN.

GSQL reads the data source JSON when the data source is created. If you change the keytab path in the JSON configuration, recreate the data source with the updated configuration so that the connector uses the new path.

The configuration properties serve the following purposes:

  • bootstrap.servers specifies the Kafka broker addresses. Use the broker FQDNs and ports configured for SASL_SSL.

  • security.protocol enables SASL authentication over TLS.

  • sasl.mechanism selects GSSAPI for Kerberos authentication.

  • sasl.kerberos.service.name specifies the Kafka service name used to construct the broker’s Kerberos service principal.

  • sasl.jaas.config configures the Kerberos login module to use the client keytab and principal.

  • ssl.truststore.type and ssl.truststore.certificates configure the PEM certificate material used to validate the brokers' TLS certificates.

The certificate property must be a JSON string. To encode a PEM certificate file as a JSON string, run:

jq -Rs . ca.pem

If the Kafka brokers require client-certificate authentication, additional TLS client-certificate properties may be necessary. Verify the supported PEM keystore properties for the Kafka connector version before configuring ssl.keystore.type, ssl.keystore.certificate.chain, and ssl.keystore.key.

Save the JSON file on the node where you run GSQL, at a path permitted by the configured local file input policy. For example, save it as /home/tigergraph/kafka_ds.json.

Create the data source:

CREATE DATA_SOURCE kafka_src = "/home/tigergraph/kafka_ds.json" FOR GRAPH my_graph

Replace my_graph with the name of your graph.

Access to local files is controlled by the GSQL.FileInputPolicy setting. Ensure that the JSON file’s path is permitted by the policy and that the file is accessible to the GSQL operation. Do not assume that a file uploaded through GraphStudio is available as a local file for this command.

After creating the data source, reference kafka_src in your Kafka loading job. For instructions on defining and running a loading job, see the existing loading-job documentation.

Verify the Connection and Troubleshoot Errors

Run the loading job to test the configuration. If the command returns a job ID, use the supported connector-status command for your TigerGraph version to inspect the job and its tasks. For example:

SHOW CONNECTOR STATUS FOR JOB <job_id>

If the loading job fails, inspect the Kafka connector logs on the relevant nodes:

gadmin log KAFKACONN

The connector log file is named kafkaconn.log.

The following table describes common errors and troubleshooting steps.

Error Possible cause and resolution

Could not login: the client is being asked for a password

The keytab may be missing, unreadable, or specified incorrectly in sasl.jaas.config. Verify the keytab path, file permissions, client principal, and accessibility on every connector node.

PortUnreachableException or Cannot contact any KDC

The connector may be unable to reach the KDC, or the configured KDC hostname, port, or transport may be incorrect. Check network access and the Kerberos configuration.

No serviceName defined

The Kafka service name may be missing from the data source configuration. Verify that sasl.kerberos.service.name is set to the service name configured for the brokers.

Server not found in Kerberos database

The broker service principal may not match the hostname used by the client. Verify the broker’s Kerberos service principal, DNS resolution, and the FQDNs specified in bootstrap.servers.

Pre-authentication information was invalid

The client principal or keytab may be incorrect or outdated. Verify that the keytab corresponds to the configured principal and check whether the principal’s keys have changed. Check the system clocks and obtain an updated keytab if necessary.

PKIX path building failed

The connector cannot build a trusted certificate chain for the broker. Verify the broker’s TLS certificate chain and ensure that the appropriate CA certificate is configured or trusted by the connector.

Schema Registry Service

To load Avro data through a schema registry service, include the schema registry details in the data source configuration:

{
  "type": "kafka",
  "bootstrap.servers": "{broker-addresses}",
  "value.converter.schema.registry.url": "{schema-registry-url}",
  "value.converter.schema.registry.basic.auth.credentials.source": "USER_INFO",
  "value.converter.schema.registry.basic.auth.user.info": "{username}:{password}",
  "value.converter":"com.tigergraph.kafka.connect.converters.TigerGraphAvroConverter"
}
Currently, only Avro schema is supported.

Create a loading job

A loading job tells the database how to construct vertices and edges from data sources. The loading job body has two parts:

  1. DEFINE statements create variables to refer to data sources. These can refer to actual files or be placeholder names. The actual data sources can be given when running the loading job.

  2. LOAD statements specify how to take the data fields from files to construct vertices or edges.

Example loading job from external Kafka

The following is an example loading job from and external Kafka cluster.

Example loading job from external Kafka
USE GRAPH ldbc_snb
CREATE DATA_SOURCE s1 = "ds_config.json" FOR GRAPH ldbc_snb
CREATE LOADING JOB load_data FOR GRAPH ldbc_snb {
  DEFINE FILENAME file_Comment = "$s1:topic_Comment";
  DEFINE FILENAME file_Person = "$s1:topic_Person";
  DEFINE FILENAME file_Comment_hasCreator_Person =
    "$s1:topic_Comment_hasCreator_Person";
  LOAD file_Comment
    TO VERTEX Comment
      VALUES ($1, $0, $2, $3, $4, $5) USING separator="|";
  LOAD file_Person
    TO VERTEX Person
      VALUES ($1, $2, $3, $4, $5, $0, $6, $7,
        SPLIT($8,";"), SPLIT($9,";")) USING separator="|";
  LOAD file_Comment_hasCreator_Person
    TO EDGE HAS_CREATOR
      VALUES ($1, $2) USING separator="|";
}

Define filenames

First we define filenames, which are local variables referring to data files (or data objects).

The terms FILENAME and filevar are used for legacy reasons, but a filevar can also be an object in a data object store.
DEFINE FILENAME syntax
DEFINE FILENAME filevar ["=" file_descriptor ];

The file descriptor can be specified at compile-time or at runtime. Runtime settings override compile-time settings:

Specifying file descriptor at runtime
RUN LOADING JOB job_name USING filevar=file_descriptor_override
While a loading job may have multiple FILENAME variables, they must all refer to the same DATA_SOURCE object.

Filename Descriptors

The file descriptor has three valid formats. You can simply provide the Kafka topic name and use default settings. Or, you can provide configuration details including the topic, either in a JSON file or as inline JSON content.

DEFINE FILENAME file_name = "$[data source name]:[topic]";
DEFINE FILENAME file_name = "$[data source name]:[json config file]";
DEFINE FILENAME file_name = "$[data source name]:[inline json content]";

For example:

// Format 1: topic only
DEFINE FILENAME file_Person = "$s1:topic_Person";

// Format 2: topic and configuration file
DEFINE FILENAME file_Person = "$s1:myfile.json";

// Format 3: topic and inline configuration
DEFINE FILENAME file_Person="""$s1:{
    "topic": "topic_Person",
    "partition.offsets": "0:0,1:3,2:-1"
}""";

To load a topic with more granular control, you can define the filename in JSON format using the parameters described in the following "Filename parameters" section.

Filename parameters

These are the required and optional configuration parameters:

Parameter Description Required? Default value

topic

The source topic name

Yes

N/A

time

The start timestamp in milliseconds to consume from. -1 means consume from the latest offset; -2 means consume from the beginning. If unset, consume from the beginning. If 'partition_offsets' is set, this setting will be ignored.

Optional

-2

partition.offsets

Specifies the partition offsets to consume from, formatted as a comma-separated list of <partition>:<offset> pairs. For example, 0:0,1:3,2:-1 indicates that partition 0 starts at offset 0, partition 1 starts at offset 3, and partition 2 starts at the latest offset. An offset of -1 means the latest offset; a offset of -2 means the beginning of the partition. If not specified, all partitions are consumed from the beginning.

Optional

N/A

tasks.max

The maximum number of tasks used to consume the source topic. You can increase this value when the source topic contains multiple partitions.

No

1

value.converter

Converter class used to convert between Kafka Connect format and the serialized form that is written to Kafka. This controls the format of the values in messages written to or read from Kafka. If records are in Avro format with Schema Registry service, use com.tigergraph.kafka.connect.converters.TigerGraphAvroConverter. If records are in Avro format without using Schema Registry service, use com.tigergraph.kafka.connect.converters.TigerGraphAvroConverterWithoutSchemaRegistry. If records are in plaintext or JSON format, use org.apache.kafka.connect.converters.ByteArrayConverter.

No

N/A

loading.timeout.ms

The loading timeout per batch in milliseconds.

If set to 0, then the global parameter RESTPP.Factory.DefaultLoadingTimeoutSec is used.

No

0

loading.max.retry.attempts

The maximum number of retries after a retryable server error.

0 means an unlimited number of retries.

No

0

io.max.retry.attempts

The maximum number of HTTP transport retries.

0 means an unlimited number of retries.

No

0

batch.size.bytes

The maximum data batch size in bytes, overriding the batch.size.rows limit.

Example: If each row of data is 1000b, batch.size.rows = 10, and batch.size.bytes = 5000, then the data will be split into 2 5000b batches.

No

2,097,152 (2 MiB)

num.partitions

[Legacy: only used when gadmin parameter KafkaConnect.EnableV2 is false] The number of partitions to use. When loading data, each partition is distributed evenly across each node. If one filename contains much more data than others, consider increasing this value.

No

3

For example:

// Example 1: Specify the start timestamp to consume from
DEFINE FILENAME file_Person="""$s1:{
    "topic": "topic_Person",
    "time": "1745203693324"
}""";

// Example 2: Specify partition offsets: partition 0 starts at offset 200, partition 1 starts at offset 100, and partition 2 starts at the latest offset
DEFINE FILENAME file_Person="""$s1:{
    "topic": "topic_Person",
    "partition.offsets": "0:200,2:100,3:-1"
}""";

  • You can increase tasks.max to process multiple files in parallel and improve loading throughput. By default, tasks.max is set to 1 to avoid resource contention when other workloads or concurrent loading jobs are running in the cluster. The effective number of tasks is limited by the number of input files:

    task_count = min(tasks.max, total_file_number)
For Avro data with schema registry, you must set both value.converter.schema.registry.url when defining the DATA_SOURCE and value.converter when defining a FILENAME.

Specify the data mapping

Next, we use LOAD statements to describe how the incoming data will be loaded to attributes of vertices and edges. Each LOAD statement handles the data mapping, and optional data transformation and filtering, from one filename to one or more vertex and edge types.

LOAD statement syntax
LOAD [ source_object|filevar|TEMP_TABLE table_name ]
  destination_clause [, destination_clause ]*
  [ TAGS clause ] (1)
  [ USING clause ];
1 As of v3.9.3, TAGS are deprecated.

Let’s break down one of the LOAD statements in our example:

Example loading job for local files
LOAD file_Person TO VERTEX Person
    VALUES($1, $2, $3, $4, $5, $0, $6, $7,
       SPLIT($8, ";"), SPLIT($9, ";"))
    USING SEPARATOR="|", HEADER="true", EOL="\n";

  • $0, $1,…​ refer to the first, second, …​ columns in each line a data file.

  • SEPARATOR="|" says the column separator character is the pipe (|). The default is comma (,).

  • HEADER="true" says that the first line in the source contains column header names instead of data. These names can be used instead of the columnn numbers.

Example using USER_DEFINED_HEADER

USER_DEFINED_HEADER specifies a header defined by DEFINE HEADER. The loader uses the user-defined column names instead of the column names in the input file.

Loading job
# define the loading job
CREATE LOADING JOB load_simprod FOR GRAPH gsql_demo {
  DEFINE HEADER head1 = "id", "hash", "words";
  DEFINE FILENAME f1;

  LOAD f1
  TO VERTEX Product VALUES ($"id", $"hash"),
  TO TEMP_TABLE t (pid, description)
    VALUES ($"id", FLATTEN($"words", ",", 1))
  USING QUOTE="double",
        HEADER="true",
        USER_DEFINED_HEADER="head1";
}

# load the data
RUN LOADING JOB load_simprod USING f1="../simprod/data/simprod_data.csv";

In this example, the input file contains the column names ProductID, ImageHash, and Description from left to right. The loading job overrides these names with id, hash, and words specified by DEFINE HEADER because USER_DEFINED_HEADER="head1" is used.

  • SPLIT is one of GSQL’s ETL functions. It says that there is a multi-valued column, which has a separator character to mark the subfields in that column.

Refer to Creating a Loading Job in the GSQL Language Reference for descriptions of all the options for loading jobs.

When loading JSON or Avro data,

  • The USING option JSON_FILE="true" must be included.

  • Refer to JSON keys (or Avro field names) instead of column index numbers.

E.g.,

LOAD file_Comment TO VERTEX Comment
  VALUES ($"id", $"content") USING JSON_FILE="TRUE"

Avro Data Validation

In version 4.2.1, users can load data in Avro format to TigerGraph DB via an external Kafka connector, such as MirrorMakerConnector, and may encounter malformed data errors during this process. See our documentation on Avro Data Validation with KafkaConnect for help.

Run the loading job

Use the command RUN LOADING JOB to run the loading job.

RUN LOADING JOB basic syntax (some options omitted)
RUN LOADING JOB [-noprint] job_name [
  USING filevar [="file_descriptor"][, filevar [="file_descriptor"]]*
  [,EOF="eof_mode"]
]

-noprint

By default, the loading job will run in the foreground and print the loading status and statistics after you submit the job. If the -noprint option is specified, the job will run in the background after displaying the job ID and the location of the log file.

filevar list

The optional USING clause may contain a list of file variables. Each file variable may optionally be assigned a file_descriptor, obeying the same format as in CREATE LOADING JOB. This list of file variables determines which parts of a loading job are run and what data files are used.

When a loading job is compiled, it generates one RESTPP endpoint for each filevar and source_object. As a consequence, a loading job can be run in parts. When RUN LOADING JOB is executed, only those endpoints whose filevar or file identifier (GSQL_FILENAME_n) is mentioned in the USING clause will be used. However, if the USING clause is omitted, then the entire loading job will be run.

If a file_descriptor is given, it overrides the file_descriptor defined in the loading job. If a particular filevar is not assigned a file_descriptor either in the loading job or in the RUN LOADING JOB statement, an error is reported and the job exits.

A loading job from an external Kafka clusters runs in streaming mode regardless of the EOF setting, i.e., it continuously read new records from the source topic.

Manage and monitor your loading job

When a loading job starts, the GSQL server assigns it a job ID and displays it for the user to see. There are four key commands to monitor and manage loading jobs:

SHOW LOADING STATUS job_id|ALL
ABORT LOADING JOB job_id|ALL
RESUME LOADING JOB job_id
SHOW LOADING ERROR job_id

SHOW LOADING STATUS shows the current status of either a specified loading job or all current jobs, this command should be within the scope of a graph:

GSQL > USE GRAPH graph_name
GSQL > SHOW LOADING STATUS ALL

For each loading job, the above command reports the following information:

  • Loading status

  • Loaded lines/Loaded objects/Error lines

  • Average loading speed

  • Size of loaded data

  • Duration

When inspecting all current jobs with SHOW LOADING STATUS ALL, the jobs in the FINISHED state will be omitted as they are considered to have successfully finished. You can use SHOW LOADING STATUS job_id to check the historical information of finished jobs. If the report for this job contains error data, you can use SHOW LOADING ERROR job_id to see the original data that caused the error.

See Managing and Inspecting a Loading Job for more details.

Manage loading job concurrency

See Loading Job Concurrency for how to manage the concurrency of loading jobs.

Known Issues with Loading

TigerGraph does not store NULL values. Therefore, your input data should not contain any NULLs.