tsload connector reference

The tsload connector APIs enable you to load data into ThoughtSpot.

Login

Use this API to authenticate and log in a user. Login establishes a session with the ThoughtSpot ETL HTTP server. The authentication requires a username and password.

Request Parameters

Form Parameter Data Type Description

username

string

ThoughtSpot username

password

string

ThoughtSpot password

Request

POST /ts_dataservice/v1/public/session HTTP/1.1
Host: client.mydomain.com Accept: application/json Content-type: application/json
{
"username":"<thoughtspot user name>",
"password":"<thoughtspot password>"
}

Response

Status: 200 OK
Set-cookie: token

Example failure responses

Status: 401 UNAUTHORIZED
Unable to verify user. Please login again.
Status: 500 INTERNAL SERVER ERROR
error code = INTERNAL, message = Couldn't resolve the authentication service.
If the cookie is not passed in subsequent calls, then requests fail.

StartLoad

After login, you use this API start the data load operation. The API call to be used here is “/ts_dataservice/v1/public/loads”. If the load is initiated successfully, the cycle ID, and the load balancer IP are returned. Once complete, you use Load to start the actual data load.

Request Parameters

Target

Specification of the target. This DB/Schema/Table must pre-exist on the destination ThoughtSpot system.

Form Parameter Data Type Description Default Value

database

string

database in ThoughtSpot

schema(optional)

string

schema in ThoughtSpot

falcon_default_schema

table

string

table in ThoughtSpot

Format

Format specifiers for parsing the input data.

Form Parameter Data Type Description Default Value

type(optional)

string

field separator character in source data. Either csv or delimited

csv

field_separator(optional)

string

input format. Either .csv or delimited

","

trailing_field_separator(optional)

boolean

True if input data has trailing field separator, false otherwise.

false

enclosing_character(optional)

string

enclosing character in csv source format. This option applies only to csv format.

"\""

escape_character(optional)

string

escape character in source data. This applies only to delimited data format. This option is ignored for other data sources.

""

null_value(optional)

string

escape character in source data. This applies only to delimited data format. This option is ignored for other data sources.

"(null)"

has_header_row(optional)

boolean

True if input data file has header row, false otherwise.

false

flexible(optional)

boolean

Whether input data file exactly matches target schema. When true, attempts to load as follows. If extra columns are present in input file, these are discarded. If fewer columns are present in input file, missing columns are filled with nulls. When false, load proceeds if input data file exactly matches target schema.

false
date_time
Form Parameter Data Type Description Default Value

converted_to_epoch(optional)

boolean

Whether date or datetime fields are already converted to epoch in source CSV. This option is ignored for other source types.

true

date_time_format(optional)

string

Format string for datetime values. Default is yearmonthday hour:minute:second e.g. Dec 30th, 2001 1:15:12 is represented as 20011230 01:15:12. System accepts date time format specifications supported in strptime datetime library.

"%Y%m%d %H:%M:%S"

date_format(optional)

string

Format string for date values. Default is yearmonthday e.g. Dec 30th, 2001 is represented as 20011230. System accepts date format specifications supported in strptime datetime library.

"%Y%m%d"

time_format(optional)

string

Format string for time values. Default is hour:minute:second System accepts time format specifications supported in strptime datetime library.

"%H:%M:%S"

skip_second_fraction(optional)

boolean

When true, skip fractional part of seconds e.g., milliseconds, microseconds or nanoseconds from datetime or time values if present in source data. This option is ignored for other source types. Note that skipping fractional component (e.g. ms) from input data can impact upsert behavior if input data has non-unique fractional values for same time or datetime values.

false
boolean
Form Parameter Data Type Description Default Value

use_bit_values(optional)

boolean

If true, source csv uses a bit for boolean values. Here in source, false is represented as 0x0 and true as 0x1. If false, boolean values are interpreted using flag boolean_representation. This option is valid for CSV

false

true_format(optional)

string

Represents True for boolean values in input.

T

false_format(optional)

string

Represents False for boolean values in input.

F

load_options

Form Parameter Data Type Description Default Value

empty_target(optional)

boolean

If true, current rows in target table or file are dropped before loading new data. If false, current rows are appended to target table or file.

false

max_ignored_rows(optional)

integer

Max number of rows that can be ignored for successful load. If number of ignored rows exceeds this limit, the load is aborted.

0

advanced_options

Form Parameter Data Type Description Default Value

max_reported_parsing_errors(optional)

integer

Maximum number of parsing errors to report back along with the status.

100

Example use of parameters

{
      target : {
          database : "<DB_NAME>",
          schema : "falcon_default_schema",
          table : "<TABLE_NAME>"
      },
      format : {
          type : "CSV",
          field_separator : ",",
          trailing_field_separator : false,
          enclosing_character : "\"",
          escape_character : "",
          null_value : "(null)",

          date_time : {
              converted_to_epoch : false,
              date_time_format : "%Y%m%d %H:%M:%S",
              date_format : "%Y%m%d",
              time_format : "%H:%M:%S",
              skip_second_fraction : false
          }
          boolean : {
              use_bit_values : false,
              true_format : "T",
              false_format : "F"
          }
          has_header_row : false,
          flexible : false
    },
    load_options : {
        empty_target : false,
        max_ignored_rows : 0,
    },
    advanced_options : {
        max_reported_parsing_errors : 100
    }
  }

Request

curl -i -X POST -b 'JSESSIONID=<GUID-XYZ>' -d '{"target_database": "<DB1>", "target_schema": "<SCHEMA1>", "target_table": "<TABLE1>", "field_separator": ",", "empty_target": false}' https://<TS_CLUSTER>:8442/ts_dataservice/v1/public/loads

Response

Status: 202 Accepted
Content-Type: text/plain
Content-Length: xx
{
  "node_address": {
    "host": "host",
    "port": port
  },
  "cycle_id": "cycle_id"
}

Example failure responses

Status: 401 UNAUTHORIZED
Unable to verify user. Please login again
Status: 403 FORBIDDEN
User does not have required privileges. Please contact your administrator.
Status: 400 BAD REQUEST
Invalid input params for starting data load: Request body
Status: 500 INTERNAL SERVER ERROR
error code = INTERNAL, message = Couldn't resolve the authentication service.

Load

Use this API to load your data. Data load can be called for multiple chunks of data for the same cycle ID. All of this data is uploaded to the ThoughtSpot cluster unless a commit load is issued.

Request

POST /ts_dataservice/v1/public/loads/<cycle_id>
Cookie: <token>
Content-Type: multipart/form-data; boundary=bndry
--bndry
Content-Disposition: form-data; name="file"; filename="sample.csv"

<CSV Data>
--bndry--
We only support multipart form/data.

Response

Status: 202 Accepted
Content-Type: text/plain
Content-Length: xx
Connection: Close
Upload Complete.

Example failure responses

Status: 401 UNAUTHORIZED
Unable to verify user. Please login again.
Status: 403 FORBIDDEN
User does not have required privileges. Please contact your administrator.
Status: 400 BAD REQUEST
Unable to find table in Falcon. Cannot load data.
Status: 400 BAD REQUEST
Cycle_id=[cycle_id] does not exist.
Status: 400 BAD REQUEST
Cannot not connect to falcon_manager.
Status: 500 INTERNAL SERVER ERROR
error code = INTERNAL, message = Couldn't resolve the authentication service.

CommitLoad

Once the data load is complete, you use CommitLoad to commit data to be loaded into the Falcon database.

Request

POST /ts_dataservice/v1/public/loads/<cycle_id>/commit
Cookie: <token>

Response

Status: 202 Accepted
Content-Type: text/plain
Content-Length: xx
Commit load cycle request made.

Example failure responses

Status: 401 UNAUTHORIZED
Unable to verify user. Please login again.
Status: 403 FORBIDDEN
User does not have required privileges. Please contact your administrator.
Status: 500 INTERNAL SERVER ERROR
Commit load cycle failed. Error ending load. Unknown cycle_id 'cycle_id'
Status: 500 INTERNAL SERVER ERROR
error code = INTERNAL, message = Couldn't resolve the authentication service.

AbortLoad

Use this API to stop loading data.

Request

POST /ts_dataservice/v1/public/loads/<cycle_id>/cancel
Cookie: token

Response

Status: 200 OK
Content-Type: text/plain
Content-Length: xx

Example failure responses

Status: 401 UNAUTHORIZED
Unable to verify user. Please login again.
Status: 403 FORBIDDEN
User does not have required privileges. Please contact your administrator.
Status: 500 INTERNAL SERVER ERROR
error code = INTERNAL, message = Couldn't resolve the authentication service.

Status of load

Use the api to get the current status of a load.

Request

GET /ts_dataservice/v1/public/loads/<cycle_id>
Cookie: token

Response

Status: 200 OK
Content-Type: text/plain
Content-Length: xxx

Example failure responses

Status: 401 UNAUTHORIZED
Unable to verify user. Please login again.
Status: 403 FORBIDDEN
User does not have required privileges. Please contact your administrator.
Status: 500 INTERNAL SERVER ERROR
error code = INTERNAL, message = Couldn't resolve the authentication service.

Data load status check logic

You can run the following code to validate that the data load is complete:

while (true) {
if (status != OK) {
   // print status.message() as the error.
} else if (internal_stage == DONE) {
   // Data load is successful
} else {
   // poll again for data load status
}
}

Bad records

Use this api to view the bad records file data.

Request

GET /ts_dataservice/v1/public/loads/<cycle_id>/bad_records_file
Cookie: token
Content-range: xxx-xxxx

Response

Status: 200 OK
Content-Type: text/plain
Content-Length: xx
Bad Records file data

Example failure responses

Status: 401 UNAUTHORIZED
Unable to verify user. Please login again.
Status: 403 FORBIDDEN
User does not have required privileges. Please contact your administrator.
Status: 500 INTERNAL SERVER ERROR
Node does not exist: /tmp/cycle_id.bad_record
Status: 500 INTERNAL SERVER ERROR
error code = INTERNAL, message = Couldn't resolve the authentication service.

Related information

Was this page helpful?
×