SeekTable is a business intelligence tool that can make PostgreSQL data available for all employees - both technical and non-technical - to query, explore, and report on. With SeekTable you can automate PostgreSql reports generation and deliver them on schedule.
Connections to PostgreSql (Redshift) are provided by SQL-compatible database connector.
There is no any limitations on the dataset size; however, your PostgreSql should be able to execute aggregate queries fast enough (in seconds; 2 mins max). In the case of large dataset you may pre-aggregate the data with materialized view, apply some filters on indexed columns, or use TimescaleDB extension for real-time aggregations.
Connection String should be a valid connection string for NpgSql driver; for example:
Host=hostName;Port=5432;Database=db;User ID=user;Password=password;
Host |
Specifies the host name of the machine on which the PostgreSql is running.
Do not use "localhost" or LAN server name; use only public IP address or server's domain name. |
---|---|
Port | The TCP port of the PostgreSQL server. |
Database | The PostgreSQL database to connect to. |
User ID | The username to connect with. |
Password | The password to connect with. |
SSL Mode | Specify SSL Mode=Require to force SSL or if your PostgreSql allows only SSL connections. |
Trust Server Certificate | Specify Trust Server Certificate=True to allow self-signed SSL server certificates. |
Server Compatibility Mode | Specify Redshift if your configure a connection to Amazon Redshift.Specify NoTypeLoading if your configure a connection to QuestDB or cube.js.
|
To define date-part dimensions (date's "year", "month", "day" etc) you may add dimensions defined with an appropriate SQL expression:
Field
EXTRACT(YEAR FROM date_column)
EXTRACT(MONTH FROM date_column)
+ Format={0:MMM}
(Jan, Feb etc) or {0:MMMM} (January, February etc)
EXTRACT(DAY FROM date_column)
EXTRACT(DOW FROM date_column)
+ Format={0:ddd}
(Mon, Tue etc) or {0:dddd}
(Monday, Tuesday etc)EXTRACT(QUARTER FROM date_column)
EXTRACT(WEEK FROM date_column)
SeekTable can access QuestDB with Postgres wire protocol that is by default accessible via 8812 port. To configure a QuestDB-based cube choose PostgreSql as a database connector and specify a connection string like this:
Host=QUEST_DB_IP;Port=8812;Database=qdb;User ID=admin;Password=quest;ServerCompatibilityMode=NoTypeLoading;
To access QuestDB on "localhost" you can easily deploy a self-hosted SeekTable version (docker).
Known limitations:
EXTRACT(YEAR FROM "column")
→ YEAR("column")EXTRACT(MONTH FROM "column")
→ MONTH("column")EXTRACT(DAY FROM "column")
→ DAY("column")SeekTable can access Cube.js with Postgres-compatible protocol that is by default accessible via 5432 port. To configure a Cube.js-based cube choose PostgreSql as a database connector and specify a connection string like this:
Host=CUBE_JS_IP;Port=5432;Database=default;User ID=test;Password=test;Server Compatibility Mode=NoTypeLoading;
Known limitations:
Trust Server Certificate=True
to the connection string to disable SSL certificate validation (needed if your server uses self-signed certificate).some_column < 5
this means that
some_column
has TEXT datatype. To fix this go to the cube configuration form, find the dimension with Name "some_column" and add a Parameter to define SQL expression with a cast:
some_column::NUMERIC
.some_column="2019-05-15"
this means that column's data type is Date (or DateTime)
and it cannot be compared with a TEXT value. To fix this go to the cube configuration form, find the dimension with Name=some_column and add a Parameter to define SQL expression with a cast;
for example:
some_column::date
, some_column::timestamp
.
ConvertInfinityDateTime=True
to the connection string;
if this doesn't help you need to exclude unpresentable timestamps from the query results (either by WHERE filtering or by
timestamp normalization with an SQL expression).
::TEXT
(custom SQL expression for the dimension may be specified in "Parameters").