Before you import data, compare the data types you want to load with these supported data types. Then, convert your data before loading it. Typically, you would export the data, transform it to meet these type rules, and then load the data. This is known as an extract-transform-load process.
Supported data types
The tables you create to receive the data must have the same number of columns and data types as the data you will be loading. Choose a data type for each column from the list of supported data types:
Data | Supported data types | Details |
---|---|---|
Character | VARCHAR(*n*) |
Specify the maximum number of characters, as in VARCHAR(255) . The size limit is 64MB for VARCHAR values. |
Floating point | DOUBLE or FLOAT |
We recommend that you use DOUBLE , because it has a range of 15 significant digits, 1.7E +/- 308 .FLOAT has a range of 7 significant digits, 3.4E +/- 38 . | s
Boolean | BOOL |
Can be true or false . |
Integer | INT or BIGINT |
INT holds 32 bits. BIGINT holds 64 bits. INT has a range of –2,147,483,648 to 2,147,483,647 . BIGINT has a range of –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 . |
Date or time | DATE , DATETIME , TIMESTAMP , TIME |
DATETIME , TIMESTAMP , and TIME are stored at the granularity of seconds. TIMESTAMP is identical to DATETIME , but is included for syntax compatibility. |
VARCHAR
. If you have any VARCHAR
data that exceeds this limit, the entire load will fail.Geographical data types
ThoughtSpot supports geographical data.
How to import geographical data
Import your geographical data as text values. This ensures that the data defaults to the correct configuration, where the data type is VARCHAR
. You can use DOUBLE
or VARCHAR
for latitude and longitude data. See the following example:
In the above example, the zipcodes were imported as text values. By default, the data type
is VARCHAR
, the column type
is attribute
, and additive
is no
. You must specify the geo config
for your data yourself. ThoughtSpot does not specify geo config automatically.
If you import zipcodes as numeric values, the column type
defaults to measure
. In ThoughtSpot, a measure
is a numeric value that you can you use in mathematical formulas. If you import your geographical data as numeric values, you must change the column type
to attribute
and specify additive
as no
.
Latitude and longitude
For latitude and longitude, you can use either VARCHAR
or DOUBLE
. Note that your latitude and longitude data must be in the form of positive and negative numbers, and not in the form of degrees. North of the Equator, latitude values are positive, and south of the Equator, latitude values are negative. East of the Prime Meridian, longitude values are positive, and West of the Prime Meridian, longitude values are negative.
Designate your geographical data in ThoughtSpot
After loading the data, designate it as a geographical data type when you Edit the system-wide data model. Wherever abbreviations or codes are used, they are the same as what the USPS (United States Postal Service) recognizes.
These data types can be designated as geographical data, which enables them to be visualized using the Geo chart types:
- Countries, for example:
- United States
long name
: United Statesname_sort
: United States of Americaabbreviation
: U.S.A.adm0_a3
: USAadm0_a3_is
: USAadm0_a3_us
: USAadmin
: United States of Americabrk_a3
: USAbrk_name
: United Statesformal_en
: United States of Americaiso_a2
: USiso_a3
: USAiso_n3
: 840
- United States
COUNTY
for counties in the United States, for example:- santa clara county
- pike county, ohio
- pike county, OH
STATE_PROVINCE
for states in the United States, for example:name
: CaliforniaUS Postal Service abbreviation
: CA
LATITUDE
, which must be used withLONGITUDE
, for example:- 37.421023
- -1.282911
LONGITUDE
, which must be used withLATITUDE
, for example:- 122.142103
- -103.848865
ZIP_CODE
for zip codes in the United States, for example:po_name
: MT MEADOWS AREAZIP
: “00012”zip2
: 12
- Other Sub-nation Regions, which are administrative regions found in countries other than the United States, for example:
- bremen
- normandy
- west midlands
Important: You cannot upload your own custom boundaries.