Because a select statement is an runtime resource, if the columns are not defined in a data definition, Tabulify would need to execute it to get and parse the response.
That's not optimal for performance reason.
So, we have implemented the below cheap detection methods to get the columns before execution.
| Method | Description | Culprit |
|---|---|---|
| describe | The data is retrieved via the DESCRIBE statement | Not supported by all database |
| false-equality | The original query gets a false condition such as 1=0 | Slow if not detected by the database |
| temporary_view | A temporary view is created | Not always possible |
| parsing | Reading and parsing of the select statement | No data type, only the column names are present Does not work with the star select * from Deprecated |
| runtime | Delay metadata detection until the statement is executed | The Sql Statement needs to return |
In this metadata detection, Tabulify will try to create a view in order to retrieve the metadata columns.
Note that not all select can be created as view:
This kind of view would fail:
select
round(sun_sales1/sun_sales2,2)
,round(mon_sales1/mon_sales2,2)
because they would get the same column name: round
Runtime metadata detection means that the select statement is executed against the database to retrieve the metadata columns.
It happens only lazily meaning that we delay the query execution until we really need a result set.
If the select statement has multiple column with the same name or alias, we add the column position so that they are unique.
For example, the below SQL
select d_week_seq1
,round(sun_sales1/sun_sales2,2)
,round(mon_sales1/mon_sales2,2)
...
would return the following column names after execution:
Tabulify does not allow 2 columns with the same and transform them like that:
You can change the list of metadata detections methods via the select-metadata-detection attribute. Setting it to a list with only runtime, disable it.
To disable query metadata detection, you need to set the select-metadata-detection attribute with only the runtime method.