Data types

ThoughtSpot supports all the common data types. Before you import data, compare the data types you want to load from the source with the ones we support. 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:

Character or Text

VARCHAR(n)

Specify the maximum number of characters, as in VARCHAR(255).

The size limit is 64MB for VARCHAR values.

If you have VARCHAR data that exceeds the 64MB limit, the entire load fails.
Floating point or Decimal

DOUBLE or FLOAT

We recommend that you use DOUBLE, because it has a range of 15 digits, 1.7E +/- 308. FLOAT has a range of 7 significant digits, 3.4E +/- 38.

Boolean

BOOL

Can be true or false.

Integer

INT or BIGINT

INT holds 32 bits. It has a range of -2,147,483,648 to 2,147,483,647.

BIGINT holds 64 bits. It 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, or TIME

ThoughtSpot stores DATETIME, TIMESTAMP, and TIME at the granularity of seconds. TIMESTAMP is identical to DATETIME; we include it for syntax compatibility.

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:

Geo data example

Here, 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 the data. ThoughtSpot does not specify geo config automatically.

  • If you import zipcodes as numeric values, the column type defaults to measure. Because measures are numeric values used in mathematical formulas and as default aggregations, 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.

Latitude and longitude data must be in the form of positive and negative numbers, most commonly decimals, NOT in degree / minute / second notation. 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.
Specifying latitude and longitude values

Designate geographical data

After loading the data, designate it as a geographical data type when you edit the system-wide data model.

  • Wherever using abbreviations or codes, ThoughtSpot conforms the USPS (United States Postal Service) standards.

  • You cannot upload your own custom boundaries.

These data types can be designated as geographical data, which enables them to be visualized using the Geo chart types:

Country

Countries, such as United States:

long name

United States
name_sort

United States of America
abbreviation

U.S.A.
adm0_a3

USA
adm0_a3_is

USA
adm0_a3_us

USA
admin

United States of America
brk_a3

USA
brk_name

United States
formal_en

United States of America
iso_a2

US
iso_a3

USA
iso_n3

840
Sub-nation region

Regions could be Countries, States, districts, zipcodes, postal codes, provinces (for example, in Canada), prefectures (in Japan), and so on.

Counties

Here are some examples of Counties:

  • santa clara county

  • pike county, ohio

  • pike county, OH

States / Provinces

Here are some examples of States and Provinces:

  • name: California

  • US Postal Service abbreviation: CA

Zip codes

Here are some examples of zip codes in the United States:

  • po_name: MT MEADOWS AREA

  • ZIP: "00012"

  • zip2: 12

Other

Here are some examples of other Sub-nation regions found in countries other than the United States:

  • bremen

  • normandy

  • west midlands

Latitude

Review the information for specifying latitude and longitude.

Here are some examples of Latitude, which you must use with Longitude:

  • 37.421023

  • -1.282911

Longitude

Review the information for specifying latitude and longitude.

Here are some examples of Longitude, which you must use with Latitude:

  • 122.142103

  • -103.848865

Related information

Was this page helpful?
×