Data types

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

DOUBLE is recommended. DOUBLE has a range of 1.7E +/- 308 (15 digits).

Boolean

BOOL

Can be true or false.

Integer

INT32 or INT64

INT32 holds 32 bits. INT64 holds 64 bits. INT32 has a range of -2,147,483,648 to 2,147,483,647. INT64 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.
There is a 64MB limitation on the number of characters for 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:

Geo data example

In the preceding image, the zip codes were imported as text values. By default, the data type is VARCHAR, the column type is attribute, and additive is no. ThoughtSpot now specifies the geo config automatically.

If you import zip codes 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, ThoughtSpot sets the geo config data under auto select. You can also 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 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

  • 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: California

    • US Postal Service abbreviation: CA

  • LATITUDE, which must be used with LONGITUDE, for example:

    • 37.421023

    • -1.282911

  • LONGITUDE, which must be used with LATITUDE, for example:

    • 122.142103

    • -103.848865

  • ZIP_CODE for zip codes in the United States, for example:

    • po_name: MT MEADOWS AREA

    • ZIP: "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

You cannot upload your own custom boundaries.

Related information

Was this page helpful?
×