SQL Based Sources
Introduction
Telmai has introduced a new feature with which users can define a query and monitor its output. This is useful when additional transformations need to be performed on the data prior to the monitoring, such as defining derived attributes, joining multiple tables etc.
Supported Integrations:
Query based sources are supported for SQL-capable databases currently supported by Telmai: Snowflake, Databricks Deltalake, BigQuery, Firebolt, Athena and Redshift. In future the support will be extended to all sources, including NoSQL databases and files
How to Create Query-based Data Source:
Click on “Configuration” -> “Connect to Data”
Choose one of the applicable source systems. For example, “Big Query”:
At this step users can choose one of the two options:
“Existing Table”
“Custom Query”
“Existing Table” - connects to an existing table or view in the database schema.
“Custom Query” - allows user to define a custom SQL query and monitor its results
Choose "Custom Query" and press "Next". You will be taken to the following screen.
Enter the new source name and edit query. Once done, click on “Validate Query” button to ensure that the query is valid. Then clicking the “Connect” button and the new source will be created.
Best Practices:
The SQL syntax is dependent on the underlying system to which we are connecting. The queries are pushed to the database. Always refer to the database syntax when using things like functions, operators etc to ensure they are supported
Always use the full table/view name, i.e. schema.table, project.dataset.table, database.schema.table or catalog.database.table. It has to be done for all tables in the query
The query can use any tables/views as long as service accounts being used to connect have permissions to these tables
It’s required to use named projections in the SQL query. I.e. use following syntax: SELECT column1 + column2 AS total FROM table1
This is needed to properly monitor the schema of the results
All queries are executed in the context of UTC time zone and hence any time/date manipulations should take that into account
Last updated
