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.

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 only. Ignored for other types. 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--

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/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.

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.

Use the tsload connector to load data