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 name | Supported db versions | Remarks |
|---|---|---|
| Amazon Aurora/RDS - MySQL | 5.5, 5.6 | cannot save views/reports that contain subqueries |
| Amazon Aurora/RDS - MySQL | 5.7 | - |
| Amazon Aurora/RDS - PostgreSQL | 9.2+ | - |
| Amazon RDS - MariaDB | 5.5+ | starting from Scai version 3.5.1+ |
| Amazon RDS - MySQL | 8 | Needs an additional connector to be installed |
| Amazon RDS - SqlServer | 2012+ | - |
| Amazon Redshift | latest | - |
| Google Cloud SQL - MySQL | 5.x | - |
| Google Cloud SQL - PostgreSQL | 9.2+ | - |
| H2 | 1.4.196+ | - |
| MariaDB | 5.5+ | starting from Scai version 3.5.1+ |
| Microsoft Azure SQL | latest | - |
| Microsoft Azure SQL (data warehouse) | latest | - |
| MySQL | 5.5, 5.6 | cannot save views/reports that contain subqueries |
| MySQL | 5.7 | - |
| MySQL | 8 | Needs an additional connector to be installed |
| PostGreSQL | 9.2+ | - |
| Snowflake | latest | starting from Scai version 3.5.0 |
| SqlServer | 2012- 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 type | MySQL | PostGreSQL | SqlServer | H2 | Redshift |
|---|---|---|---|---|---|
| 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 |