If you would like to learn the basics for importing your data into Trends AI, please read this article first!

Data types

Trends AI supports all common data types. Before you import data, compare the data types you want to load from the source with the ones we support, and 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 (ETL) 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 for your field, such as VARCHAR(255). Trends AI has a size limit of 64MB for VARCHAR values.

If any VARCHAR data exceeds the 64MB limit, the entire data load will fail.

Floating point or Decimal

DOUBLE or FLOAT

We recommend using DOUBLE, as it offers a range of 15 significant digits ($1.7E \pm 308$). In comparison, FLOAT provides a range of 7 significant digits ($3.4E \pm 38$).

Boolean

BOOL

This data type represents logical values and can be set to either true or false.

Integer

INT or BIGINT

INT holds 32 bits, with a range from -2,147,483,648 to 2,147,483,647.
BIGINT holds 64 bits, with a range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Date or time

DATE, DATETIME, TIMESTAMP, or TIME

Trends AI stores DATETIME, TIMESTAMP, and TIME with a precision down to the second. Please note that TIMESTAMP is identical to DATETIME; it is included to ensure compatibility with your existing syntax.

Geographical data types

Trends AI supports geographical data, making it easy to visualize location-based information.

How to import geographical data

Import your geographical data as text values. This ensures that the data defaults to the correct configuration.

For example, if you import zip codes 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

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.

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, Trends AI conforms to the USPS (United States Postal Service) standards. You cannot import 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
Sub-nation region: State, County, Zip Code, Province, Prefecture, District, etc.
Latitude
Longitude

Country

This category includes countries, such as the United States. Below are the supported formats for country identification:

Format Type	Example
`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 can include states, districts, zip codes, postal codes, provinces (such as those in Canada), and prefectures (such as those in Japan).

Counties: Examples include santa clara county, pike county, ohio, or pike county, OH.
States / Provinces: Examples include using the full name (California) or the US Postal Service abbreviation (CA).
Zip codes: For United States zip codes, you can use the po_name (MT MEADOWS AREA), the standard ZIP ("00012"), or zip2 (12).
Other: Examples of sub-nation regions outside of the United States include bremen, normandy, and west midlands.

Latitude

When using latitude, please ensure you review the requirements for specifying latitude and longitude. Latitude must always be used in conjunction with longitude.

Examples: 37.421023, -1.282911

Longitude

When using longitude, please ensure you review the requirements for specifying latitude and longitude. Longitude must always be used in conjunction with latitude.