Databases

Scai connects to multiple SQL databases seamlessly with a click of a button. Below, you can find all the versions and the supported data types that Scai supports.

Supported databases

Database nameSupported db versionsRemarks
Amazon Aurora/RDS - MySQL5.5, 5.6cannot save views/reports that contain subqueries
Amazon Aurora/RDS - MySQL5.7-
Amazon Aurora/RDS - PostgreSQL9.2+-
Amazon RDS - MariaDB5.5+starting from Scai version 3.5.1+
Amazon RDS - MySQL8Needs an additional connector to be installed
Amazon RDS - SqlServer2012+-
Amazon RedshiftlatestNeeds an additional connector to be installed
Google Cloud SQL - MySQL5.x-
Google Cloud SQL - PostgreSQL9.2+-
H21.4.196+-
MariaDB5.5+starting from Scai version 3.5.1+
Microsoft Azure SQLlatest-
Microsoft Azure SQL (data warehouse)latest-
MySQL5.5, 5.6cannot save views/reports that contain subqueries
MySQL5.7-
MySQL8Needs an additional connector to be installed
PostGreSQL9.2+-
Snowflakelateststarting from Scai version 3.5.0
SqlServer2012- 2017-latest-

Supported data types

One of the advantages of using Scai is the fact that it automatically detects the data types, which makes it easy to connect and explore the data. Each of the supported databases has its own particularity and its own restrictions. We will try to document as follows, some of the data types that Scai does not support or where it makes appropriate conversions.

Legend

the data type is supported by Scai and within the particular database

the data type is not supported by Scai

the data type is not supported by the particular SQL database

Data typeMySQLPostGreSQLSqlServerH2Redshift
bigint
bigserial
bit
bool
boolean
box
bpchar
char
character
cidr
circle
clob
date
datetime
dec
decimal
double
enum
fixed unsigned
float
geometry
geometrycollection
identity
int
integer
json
linestring
longtext
longvarchar
lseg
macaddr
mediumint
mediumtext
money
multilinestring
multipoint
multipolygon
national char
nchar
nclob
number
numeric
numrange
nvarchar
path
point
polygon
real
serial
set
signed
smalldatetime
smallint
smallserial
text
time
timestamp
tinyint
tinytext
tsquery
tsrange
tstzrange
tsvector
txid_snapshot
uniqueidentifier
uuid
varbit
varchar
year
xml

Supported aggregate functions

Scai lets you aggregate data intuitively through its visual interface. Since the aggregation itself (and the processing of data) is performed by the underlying database system, Scai allows you to choose from a set of database supported aggregated functions when you try to add any Measurements. The following table summarizes which aggregated functions are supported by Scai for each database vendor.

| Database vendor | Aggregated functions | More info || ---- | ---- | ---- | ---- | ---- | ---- || H2SQL | count, sum, avg, min, max, every, some, stddev_samp, stddev_pop, var_samp, var_pop, selectivity, bit_and, bit_or | H2 functions || MySQL | count, sum, avg, min, max, group_concat, stddev_samp, stddev_pop, var_samp, var_pop, bit_and, bit_or, bit_xor | MySQL aggregate functions || SqlServer | count, sum, avg, min, max, string_agg, stdev, stdevp, var, varp | SqlServer aggregate functions || PostgreSQL | count, sum, avg, min, max, string_agg, stddev_samp, stddev_pop, var_samp, var_pop, bool_and, bool_or, bit_and, bit_or | PostgreSQL aggregate functions || Redshift | count, approximate count, sum, avg, min, max, median, listagg, stddev_samp, stddev_pop, var_samp, var_pop | Redshift aggregate functions |