---
title: "Read to Spark Dataframe"
component: "tigergraph-server"
version: "3.11"
module: "data-loading"
html_url: "/tigergraph-server/3.11/data-loading/read-to-spark-dataframe"
canonical_url: "https://www.tigergraph.com/docs/tigergraph-server/3.11/data-loading/read-to-spark-dataframe"
markdown_url: "https://www.tigergraph.com/docs/tigergraph-server/3.11/data-loading/read-to-spark-dataframe.md"
---

[View as HTML](https://www.tigergraph.com/docs/tigergraph-server/3.11/data-loading/read-to-spark-dataframe)

# Read to Spark Dataframe

The TigerGraph Spark Connector employs[Apache Spark](https://spark.apache.org) to read data from TigerGraph. The same connector can be used as an intermediary to read data from a variety of sources and then[stream data from Spark to TigerGraph.](https://www.tigergraph.com/docs/tigergraph-server/3.11/data-loading/load-from-spark-dataframe.md)

Supported reader types:

* Read Vertices
* Read Edges
* Run Pre-installed Query
* Run Interpreted Query

> [!NOTE]
> The legacy [Spark Connection Via JDBC Driver](https://www.tigergraph.com/docs/tigergraph-server/3.11/data-loading/spark-connection-via-jdbc-driver.md) is deprecated. Please migrate to this new connector.

## Prerequisite

* TigerGraph 3.6.0 or higher. Job-level loading statistics are available in v3.10+.
* Spark 3.2 or higher with Scala 2.12 and Scala 2.13.
* JAVA 8 or higher.

### Download the TigerGraph Spark Connector

This connector can be downloaded from the Maven central repository for[Scala 2.12](https://central.sonatype.com/artifact/com.tigergraph/tigergraph-spark-connector%5F2.12/overview) and [Scala 2.13](https://central.sonatype.com/artifact/com.tigergraph/tigergraph-spark-connector%5F2.13/overview)

The connector is available in three formats:

* `tigergraph-spark-connector-<version>.jar`: The `JAR` file containing only the compiled classes of the connector, which does not include any dependencies.
* `tigergraph-spark-connector-<version>-jar-with-dependencies.jar`: The `JAR` file that includes compiled classes, as well as all the dependencies.
* `tigergraph-spark-connector-<version>.tar.gz`: The compressed `TAR` archive that includes `tigergraph-spark-connector-<version>.jar` and dependencies in separate `JAR` files.

To use the TigerGraph Spark connector in a Spark shell:

use the `--jars` option:

spark-shell --jars tigergraph-spark-connector-<version>-jar-with-dependencies.jar

> [!NOTE]
> If you want to include the TigerGraph Spark Connector in your Spark installation, add the `JAR` with dependencies to Spark's `jars` folder.

### Configure Logging

We highly recommend setting the log level to `info` to monitor the execution. There are two methods to enable logging for the connector.

#### Connector Default Logger

The connector has a built-in logger based on _java.util.logging_. To activate it, set the option `log.level` to 2, which enables info-level logging.

Please refer to [Common Options](https://www.tigergraph.com/docs/tigergraph-server/3.11/data-loading/read-to-spark-dataframe.md#_common_options) to learn more about `log.level` and `log.file` options.

#### Spark Logger

Configure Spark Log4j by `$SPARK_HOME/conf/log4j2.properties` as follows, or other SLF4j bindings if any:

logger.tg.name = com.tigergraph
logger.tg.level = info

## Overview

TigerGraph Spark connector provides 4 kinds of reader options to read data from TigerGraph:

| Option | Description |
| --- | --- |
| `query.vertex` | Read vertex/vertices with the attributes. |
| `query.edge` | Read edge(s) with the attributes. |
| `query.installed` | Run a pre-installed query and read the outputs of `PRINT` statement. |
| `query.interpreted` | Run a query in interpreted mode and read the outputs of `PRINT` statement. |

Please refer to the following sections for detailed usage and examples. Prior to that, ensure that the connection options are defined in advance.

### Database Connection

> [!NOTE]
> Connection options object
> 
> You may find it convenient to bundle the options related to making a database connection in a data object (e.g., `tgOptions`) and then use `.options(tgOptions)` when running the connector. Moreover, placing the user credentials in a data object keeps them separate from the connection commands.

Predefined connection options:

```scala
val tgOptions = Map(
    "url" -> "http(s)://<tg_node_host>:<tg_nginx_port>",
    "version" -> "<tg_version>",
    "graph" -> "<graph_name>",
    "username" -> "<username>",
    "password" -> "<password>"
)
```

> [!NOTE]
> If you're running long queries through the Spark connector and a load balancer (for example, Azure Load Balancer) is between Spark and TigerGraph, increase the load balancer's idle timeout to prevent the connection from timing out.

> [!NOTE]
> Authentication methods
> 
> Using username/password has the advantage that this authentication method will automatically refresh an access token as needed.
> 
> If you choose to use a token, please make sure the lifetime of the token is long enough for the loading job.

## Read Vertices

### Read all vertices by type

| Option | Value |
| --- | --- |
| `query.vertex` | <vertex\_type> |

Example: read all vertices of type `Person`

```scala
val df = spark.read
    .format("tigergraph")
    .options(tgOptions)
    .option("query.vertex", "Person")
    .load()

df.show()
```

Results

```none
+--------+--------+---+------+-----+
|    v_id|    name|age|gender|state|
+--------+--------+---+------+-----+
|person42|person42| 42|female|   ny|
|person12|person12| 12|  male|   ny|
+--------+--------+---+------+-----+
```

### Partitioned Vertex Query

To fully utilize the parallel and distributed processing capabilities of Spark and TigerGraph, it is recommended to use partitioned queries. This approach involves splitting a query into multiple range queries that are executed in parallel.

| Option | Value |
| --- | --- |
| `query.partition.key` | The name of the attribute that has been indexed. |
| `query.partition.num` | The expected partition number. |
| `query.partition.upper.bound` | The upper bound used to calculate the partition stride. |
| `query.partition.lower.bound` | The lower bound used to calculate the partition stride. |

> [!NOTE]
> Notice that `query.partition.upper.bound` and `query.partition.lower.bound` are just used to decide the partition stride, not for filtering the vertices.

Example

```scala
val df = spark.read
    .format("tigergraph")
    .options(tgOptions)
    .option("query.vertex", "Person")
    .option("query.partition.key", "age")
    .option("query.partition.num", "5")
    .option("query.partition.upper.bound", "50")
    .option("query.partition.lower.bound", "20")
    .load()

df.show()
```

This query will generate 5 separate range queries:

* age < 20
* 20 ≤ age < 30
* 30 ≤ age < 40
* 40 ≤ age < 50
* 50 ≤ age

### Read a vertex by primary ID

| Option | Value |
| --- | --- |
| `query.vertex` | <vertex\_type>.<vertex\_id> |

Example: read a vertex of type `Person` and primary ID `person2`

```scala
val df = spark.read
    .format("tigergraph")
    .options(tgOptions)
    .option("query.vertex", "Person.person2")
    .load()

df.show()
```

Results

```none
+--------+--------+---+------+-----+
|    v_id|    name|age|gender|state|
+--------+--------+---+------+-----+
| person2| person2|  3|  male|   tx|
+--------+--------+---+------+-----+
```

## Read Edges

### Read all edges of a vertex

| Option | Value |
| --- | --- |
| `query.edge` | <src\_vertex\_type>.<src\_vertex\_id> |

Example: read all edges of vertex type `Person`, ID `person1`

```scala
val df = spark.read
    .format("tigergraph")
    .options(tgOptions)
    .option("query.edge", "Person.person1")
    .load()

df.show()
```

Results

```none
+---------+-------+-------+-------+-------+--------------------+
|from_type|from_id|to_type|  to_id| e_type|          attributes|
+---------+-------+-------+-------+-------+--------------------+
|   Person|person1|  Photo|photo16|  Likes|{"click_time":"20...|
|   Person|person1|  Short| short1|  Likes|{"click_time":"20...|
|   Person|person1|  Short| short1|Watches|      {"share":true}|
|   Person|person1|  Short|short19|Watches|      {"share":true}|
+---------+-------+-------+-------+-------+--------------------+
```

The results can contain different types of edges, so the edge attributes won't be flattened.

### Read edges of a vertex by edge type

| Option | Value |
| --- | --- |
| `query.edge` | <src\_vertex\_type>.<src\_vertex\_id>.<edge\_type> |

Example: read all edges of type `Likes` from vertex type `Person`, ID `person1`

```scala
val df = spark.read
    .format("tigergraph")
    .options(tgOptions)
    .option("query.edge", "Person.person1.Likes")
    .load()

df.show()
```

Results

```none
+---------+-------+-------+-------+-------------------+
|from_type|from_id|to_type|  to_id|         click_time|
+---------+-------+-------+-------+-------------------+
|   Person|person1|  Photo|photo16|2024-01-22 03:16:45|
|   Person|person1|  Short| short1|2019-07-15 09:23:41|
+---------+-------+-------+-------+-------------------+
```

### Read edges of a vertex by edge type and target vertex type

| Option | Value |
| --- | --- |
| `query.edge` | <src\_vertex\_type>.<src\_vertex\_id>.<edge\_type>.<tgt\_vertex\_type> |

Example: read all edges of type `Likes` from vertex type `Person`, ID `person1` to vertex type `Photo`

```scala
val df = spark.read
    .format("tigergraph")
    .options(tgOptions)
    .option("query.edge", "Person.person1.Likes.Photo")
    .load()

df.show()
```

Results

```none
+---------+-------+-------+-------+-------------------+
|from_type|from_id|to_type|  to_id|         click_time|
+---------+-------+-------+-------+-------------------+
|   Person|person1|  Photo|photo16|2024-01-22 03:16:45|
|   Person|person1|  Photo| photo7|2021-05-09 17:58:00|
+---------+-------+-------+-------+-------------------+
```

### Read an edge by source vertex, target vertex, and edge type

| Option | Value |
| --- | --- |
| `query.edge` | <src\_vertex\_type>.<src\_vertex\_id>.<edge\_type>.<tgt\_vertex\_type>.<tgt\_vertex\_id> |

Example: read an edge of type `Likes` from vertex type `Person` and ID `person1`, to vertex type `Photo` and ID `photo7`

```scala
val df = spark.read
    .format("tigergraph")
    .options(tgOptions)
    .option("query.edge", "Person.person1.Likes.Photo.photo7")
    .load()

df.show()
```

Results

```none
+---------+-------+-------+------+-------------------+
|from_type|from_id|to_type| to_id|         click_time|
+---------+-------+-------+------+-------------------+
|   Person|person1|  Photo|photo7|2021-05-09 17:58:00|
+---------+-------+-------+------+-------------------+
```

> [!NOTE]
> By default, we use "." to split the query fields. If the vertex ID contains ".", you can set a different separator to avoid the conflict:
> 
> ```scala
> .option("query.edge", "Person|jack.smith|Likes|Photo")
> .option("query.field.separator", "|")
> ```

## Query Operators

For the built-in vertex and edge queries mentioned above, there are several available query operators that can be used in combination.:

| Option | Value | Example |
| --- | --- | --- |
| `query.op.select` | Attributes of the selected vertices/edges to return separated by comma. | `name,age` |
| `query.op.filter` | Conditions used to filter the vertices/edges to return. The parameter takes a list of conditions, which is a string of comma-separated values. | `age>20,gender=male` |
| `query.op.limit` | An integer value that specifies the maximum limit of the total number of vertices/edges to return. NOTE: When used with partitioned queries, the limit applies to each partition. | `50` |
| `query.op.sort` | Attributes to sort the results by. The parameter takes a list, which is a string of comma-separated values, and will sort all the vertices/edges based on the attributes provided in the list in order. NOTE: When used with partitioned queries, the sort applies within each partition. | `age,-salary` |

Example: read top 10 vertices of type Person, sorted by age(ASC) and last\_name(DESC)

```scala
val df = spark.read
    .format("tigergraph")
    .options(tgOptions)
    .option("query.vertex", "Person")
    .option("query.op.limit", "10")
    .option("query.op.sort", "age,-last_name")
    .load()

df.show()
```

Example: read edges of type `Invest`, whose `ratio` attribute should be greater than 0.1, and select only the `timestamp` attribute:

```scala
val df = spark.read
    .format("tigergraph")
    .options(tgOptions)
    .option("query.edge", "Person.person1.Invest")
    .option("query.op.filter", "ratio>0.1")
    .option("query.op.select", "timestamp")
    .load()

df.show()
```

## Run a GSQL Query

### Run a Pre-installed Query

| Option | Value |
| --- | --- |
| `query.installed` | Name of the pre-installed query. |
| `query.params` | The query parameters in JSON format. Please refer to [Formatting data in JSON](https://www.tigergraph.com/docs/tigergraph-server/3.11/API/index.md#_formatting_data_in_json). |

Example: run the query `topK` with k=10

GSQL Query `topK`

```gsql
CREATE QUERY topK (INT k) {
    vSet = SELECT p FROM Person:p ORDER BY p.age LIMIT k;
    PRINT vSet;
}
```

Reader Options

```scala
val df = spark.read
    .format("tigergraph")
    .options(tgOptions)
    .option("query.installed", "topK")
    .option("query.params", "{\"k\": 10}")
    .load()

df.show()
```

### Run an Interpreted Query

| Option | Value |
| --- | --- |
| `query.interpreted` | Query body of an anonymous query. |

Example: run the anonymous `topK` query in interpreted mode with k=10\. Because no parameter is allowed, the parameter k is set within the query.

Define the interpreted query body

```scala
val queryBody = """
    INTERPRET QUERY () FOR GRAPH gsql_demo {
        INT k=10;
        vSet = SELECT p FROM Person:p ORDER BY p.age LIMIT k;
        PRINT vSet;
    }
"""
```

Reader Options

```scala
val df = spark.read
    .format("tigergraph")
    .options(tgOptions)
    .option("query.interpreted", queryBody)
    .load()

df.show()
```

### DataFrame Schema

#### Schema Inference

The output format of a GSQL query is flexible, as you can include multiple `PRINT` statements within a single query, with each `PRINT` statement capable of printing multiple objects.

By default, the output of each `PRINT` statement is converted into a row of a DataFrame:

A portion of a GSQL query with multiple PRINT statements

```gsql
...
ListAccum<INT> @@list_accum = [1,3,5];
PRINT @@list_accum, @@list_accum.size();
PRINT @@list_accum.get(0);
...
```

The resulting DataFrame includes two rows corresponding to the outputs of two PRINT statements.

```none
+------------------------------------------------+
|results                                         |
+------------------------------------------------+
|{"@@list_accum":[1,3,5],"@@list_accum.size()":3}|
|{"@@list_accum.get(0)":1}                       |
+------------------------------------------------+
```

However, sometimes we want to convert a vertex expression set or an accumulator into a Spark DataFrame, like `@@list_accum`, with each element being transformed into a row of the DataFrame.

The connector can automatically expand the vertex set or accumulator to Spark DataFrame under the following conditions:

* It is an installed query instead of an interpreted query;
* It contains only one PRINT statement, which only print a single object;
* The printed object must either be a vertex expression set, or an accumulator.

You can also explicitly extract an object from multiple PRINT statements by option `query.results.extract`:

| Option | Value |
| --- | --- |
| `query.results.extract` | The row index and the object key concatenated with colon. E.g.: `1:vSet`, which extracts `vSet` from the output of the 2nd PRINT statement. |

Example: The above list accumulator is extracted and expanded by setting "query.results.extract" to "0:@@list\_accum".

```none
+-------+
|results|
+-------+
|1      |
|3      |
|5      |
+-------+
```

#### Custom Schema

For interpreted queries and some installed queries, the schema cannot be inferred accurately, so it's required to manually provide the schema by `.schema(DDLString)`, whose column names should be the same as the corresponding JSON keys. Please check the example below:

Define the topK query in interpreted mode

```scala
val queryBody = """
    INTERPRET QUERY () FOR GRAPH gsql_demo {
        INT k=3;
        vSet = SELECT p FROM Person:p ORDER BY p.age LIMIT k;
        PRINT vSet;
    }
"""
```

1\. without user given schema

```scala
val df = spark.read
    .format("tigergraph")
    .options(tgOptions)
    .option("query.interpreted", queryBody)
    .option("query.results.extract","0:vSet") // explicitly expand the vertex set
    .load()

df.show()
+-----------------------------------------------------------------------------------------------------------+
|results                                                                                                    |
+-----------------------------------------------------------------------------------------------------------+
|{"v_id":"person1","attributes":{"gender":"male","name":"person1","state":"fl","age":1},"v_type":"Person"}  |
|{"v_id":"person2","attributes":{"gender":"female","name":"person2","state":"ca","age":2},"v_type":"Person"}|
|{"v_id":"person3","attributes":{"gender":"male","name":"person3","state":"ny","age":3},"v_type":"Person"}  |
+-----------------------------------------------------------------------------------------------------------+
```

2\. with user given schema

```scala
val df = spark.read
    .format("tigergraph")
    .schema("v_id STRING, gender STRING, name STRING, state STRING, age INT") // user given schema
    .options(tgOptions)
    .option("query.interpreted", queryBody)
    .option("query.results.extract","0:vSet")  // explicitly expand the vertex set
    .load()

df.show()
+-------+------+-------+-----+---+
|v_id   |gender|name   |state|age|
+-------+------+-------+-----+---+
|person1|male  |person1|fl   |1  |
|person2|female|person2|ca   |2  |
|person3|male  |person3|ny   |3  |
+-------+------+-------+-----+---+
```

## Connector Options

### Common Options

> [!NOTE]
> Beginning with version 0.2.0 of the connector, the following [connector options](https://www.tigergraph.com/docs/tigergraph-server/3.11/data-loading/load-from-spark-dataframe.md#_connector_options) are required: `version`, `log.level`, and `log.file`.

| Key | Default Value | Description | Group |
| --- | --- | --- | --- |
| `url` | (none) | The connection URL to TigerGraph cluster. It can be a list of URLs separated by comma for load balancing. The port number can be retrieved by `gadmin config get Nginx.Port`. For TGCloud users, the default port number is 443. Example: http(s)://192.168.1.1:14240, http(s)://192.168.1.2:14240, http(s)://192.168.1.3:14240 | General |
| `version` | (none) | TigerGraph version, e.g., "3.10.1", "4.1.0". | General |
| `graph` | (none) | The graph name. | General |
| `username` | (none) | The GSQL username. | Authentication (The username/password pair or secret based authentication is more preferred than a fixed token, as it can generate and refresh token automatically.) |
| `password` | (none) | The GSQL password. | Authentication |
| `secret` | (none) | The GSQL secret, which is recommended for TGCloud users. | Authentication |
| `token` | (none) | The classic Bearer token or JWT(since 3.10.1) for RESTPP authentication. | Authentication |
| `ssl.mode` | basic | The SSL mode. Supported values: `basic`, `verifyCA` and `verifyHostname`. When setting it to `verifyCA` or `verifyHostname`, the truststore file should be given. | SSL |
| `ssl.truststore` | (none) | Filename of the truststore which stores the SSL certificate chains. Add `--files /path/to/trust.jks` when submitting the Spark job. | SSL |
| `ssl.truststore.type` | JKS | Truststore type, e.g., JKS, PKCS12 | SSL |
| `ssl.truststore.password` | (none) | Password of the truststore. | SSL |
| `io.connect.timeout.ms` | 30000 | Connect timeout in ms. | Transport Timeout |
| `io.read.timeout.ms` | 60000 | Socket read timeout in ms. | Transport Timeout |
| `io.retry.interval.ms` | 5000 | The initial retry interval for transport timeout. | Transport Timeout |
| `io.max.retry.interval.ms` | 10000 | The maximum retry interval for transport timeout. | Transport Timeout |
| `io.max.retry.attempts` | 5 | The maximum retry attempts for transport timeout. | Transport Timeout |
| `log.level` | (none) | The log level of the default logger. Available values: "0", "1", "2" and "3", which represent "ERROR", "WARN", "INFO" and "DEBUG". The Spark logging configurations(log4j) will be omitted if this option is set. The log will be printed to stderr unless `log.file` is set. | Logging |
| `log.file` | (none) | The log file name pattern, e.g., "/tmp/tigergraph-spark-connector.log". It requires setting `log.level` first. | Logging |

### Query Options

| Key | Default Value | Description | Group |
| --- | --- | --- | --- |
| `query.timeout.ms` | 16000 | By default, an HTTP request in the TigerGraph system times out after 16 seconds. You can customize this timeout limit for a particular query instance by this option. Equivalent to RESTPP request header `GSQL-TIMEOUT`. | Query |
| `query.max.response.bytes` | (none) | Specify the response size limit of a query request. Equivalent to RESTPP request header `RESPONSE-LIMIT`. | Query |
