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 |
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 |
---|---|---|---|
|
string |
field separator character in source data. Either |
csv |
|
string |
input format. Either |
"," |
|
boolean |
True if input data has trailing field separator, false otherwise. |
false |
|
string |
enclosing character in csv source format. This option applies only to csv format. |
"\"" |
|
string |
escape character in source data. This applies only to delimited data format. This option is ignored for other data sources. |
"" |
|
string |
escape character in source data. This applies only to delimited data format. This option is ignored for other data sources. |
"(null)" |
|
boolean |
True if input data file has header row, false otherwise. |
false |
|
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 |
---|---|---|---|
|
boolean |
Whether date or datetime fields are already converted to epoch in source CSV. This option is ignored for other source types. |
true |
|
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" |
|
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" |
|
string |
Format string for time values. Default is hour:minute:second System accepts time format specifications supported in strptime datetime library. |
"%H:%M:%S" |
|
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 |
---|---|---|---|
|
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 |
|
string |
Represents True for boolean values in input. |
T |
|
string |
Represents False for boolean values in input. |
F |
load_options
Form Parameter | Data Type | Description | Default Value |
---|---|---|---|
|
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 |
|
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 |
---|---|---|---|
|
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.
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.
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.
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.
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
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