Data types
DataLens uses different data sources with various field data types.
To streamline the data operations, DataLens converts the source data types to its own types.
This optimizes data operations. Learn more in Data type matching table.
You can change the field data type in the dataset interface and in the wizard.
Warning
When you load a CSV file as a data source, DataLens tries to automatically detect the types of data in its fields.
If it can't detect the data type, DataLens sets the String
data type for the field.
You can use the dataset interface or the wizard to change the field data type.
Geopoint
A coordinate point defined by latitude and longitude. In DataLens, it's set using the function GEOPOINT.
As the input, the function accepts the String
and Geocache
data types, or two values of the Fractional number
or String
type.
If a single string is input, it must contain a list of two numbers in JSON format.
If the source data is a string like "[55.75222,37.61556]"
, you can use the dataset interface or wizard to change the field data type without a formula.
Record example
GEOPOINT("[55.7912,37.6872]")
GEOPOINT("[55.8538,37.6312]")
Geopolygon
Several coordinate points defining the polygon on the map. You can define a polygon in DataLens using the function GEOPOLYGON.
The Even-Odd algorithm is used to fill in the polygon. This way you can create polygons with holes.
The function accepts strings in the format "[[[v1,v1], [v2,v2]], ..., [[vN-1,vN-1], [vN,vN]]]"
. If the source data is in this format, you can use the dataset interface or wizard to change the field data type without a formula.
Record example
/* Polygon without a hole */
GEOPOLYGON("[[[55.79421,37.65046],[55.79594,37.6513],[55.79642,37.65133],[55.7969, 37.65114],[55.79783, 37.65098],[55.78871,37.75101]]]")
/* Polygons with a hole */
GEOPOLYGON("[[[55.75,37.52],[55.75,37.68],[55.65,37.60]],[[55.79,37.60],[55.76,37.57],[55.76,37.63]]]")
GEOPOLYGON("[[[55.75,37.50],[55.80,37.60],[55.75,37.70],[55.70,37.70],[55.70,37.50]],[[55.75,37.52],[55.75,37.68],[55.65,37.60]],[[55.79,37.60],[55.76,37.57],[55.76,37.63]]]")
Date
Date without specified time.
When you use date in formulas, make sure to use hash #
around it. For example, DATETRUNC(#2018-07-12#, "year", 5)
.
You can convert the source data type to Date
by using the functions DATE and DATE_PARSE.
Record example
#2018-01-18#
#2015-01-01#
DATETRUNC(#2018-07-12#, "year", 5)
DATEADD(#2018-01-12#, "day", 6)
Date and time
Date with specified time.
When you use date and time in formulas, make sure to use a hash #
around it. For example, DATEADD(#2018-01-12 01:02:03#, "second", 6)
.
You can convert the source data type to Date and time
by using the functions DATETIME and DATETIME_PARSE.
Record example
#2018-01-12 01:08:03#
#2018-05-01T#
DATEADD(#2018-01-12 01:02:03#, "second", 6)
DATETRUNC(#2018-07-12 11:07:13#, "month", 4)
Fractional number
Decimal number. The dot is used as a decimal separator.
You can convert the source data type to a Fractional number
by using the function FLOAT.
Warning
When you convert the decimal
type to a Fractional number
, you may lose accuracy.
Record example
1.47113
0.62024
FLOAT("34.567")
Logical
A logical type that can be either TRUE
or FALSE
.
You can convert the source data type to Logical
by using the function BOOL.
Record example
FALSE
String
A string containing text. It is enclosed in single or double quotes.
If you enclose your string in one type of quotes, you can use the second quote type inside the string without escaping.
For example, 'Double quoted "example" and one " double quote.'
You can use the following characters inside strings:
\n
: Line feed (LF).\r
: Carriage return (CR).\t
: Tab.\"
: Double quote.\'
: Single quote.\\
: Backslash.
You can convert the source data type to String
by using the function STR.
Record example
"String"
'String with a "substring"'
Integer
A number with no no fractional part.
You can convert the source data type to Integer
by using the function INT.
Record example
42
157
Data type matching table
When you create a dataset, DataLens converts the source data to its own type for streamlined data processing.
This helps DataLens unify the operations with data from different sources.
The table below shows how data types of different databases and DataLens internal data types correspond to one another.
DataLens | Materialized dataset |
ClickHouse | PostgreSQL | MySQL | MS SQL |
---|---|---|---|---|---|
Logical | boolean | boolean | boolean | bit | bit |
Date | date | date | date | date | date |
Date and time | datetime | datetime | timestamp | datetime timestamp |
datetime datetime2 smalldatetime datetimeoffset |
Fractional number | float | float float32 float64 decimal* |
real double precision numeric |
float double numeric decimal* |
float real numeric decimal* |
Integer | int64 | integer int8 int16 int32 int64 uint8 uint16 uint32 uint64 |
smallint integer bigint |
tinyint smallint mediumint integer bigint |
tinyint smallint integer bigint |
String | string | string enum8 enum16 |
char varchar text |
tinyblob blob binary varbinary char varchar tinytext text enum |
char varchartext nchar nvarchar ntext |
Geopoint | Set by a formula in DataLens | Set by a formula in DataLens | Set by a formula in DataLens | Set by a formula in DataLens | Set by a formula in DataLens |
Geopolygon | Set by a formula in DataLens | Set by a formula in DataLens | Set by a formula in DataLens | Set by a formula in DataLens | Set by a formula in DataLens |
* You may lose accuracy when converting data.