Snowflake
Snowflake is a solution for data warehousing, data lakes, data engineering, data science, data application development, and securely sharing and consuming shared data.
Whilst you can add primary keys and use Snowflake for OLTP, by default tables do not have primary keys.
Particularly within Budibase, the Snowflake connector is designed for Custom queries, thus is most appropriate for OLAP purposes.
https://www.snowflake.com/guides/olap-vs-oltp
Setup
Snowflake has the following hierarchy for data:
- Warehouse
- Database
- Schema
- Table
To use the Snowflake connector in Budibase, you will need to have each of these elements.
Upon creation of a new Snowflake account, there will be sample data that you can use to verify your connection.
Warehouses can be found and added under the Admin tab. The default warehouse is COMPUTE_WH.
You will also need your account name. This can be found under Admin > Accounts.
Click on the clip icon beside the account. The account name will be your snowflakecomputing subdomain, in this case: hffwdhe-bn85610
Connect
When you add a new Snowflake Data Source you will be presented with a config dialog to fill in.
Query
Snowflake can perform the standard SQL queries as you would expect, however it also has some nifty syntax to query JSON as well.
The sample WEATHER schema contains a JSON object with a nested array of weather information.
This can be seen when running a query via the SnowSQL CLI
This structure can be flattened as needed, and then queried using the :
notation to grab nested properties.
https://docs.snowflake.com/en/user-guide/querying-semistructured.html
You can also see how Bindings can be used, in this case as a filter.
It would be possible to Transform the data rather than do the mapping as part of the query, however this is not advisable as the performance will be much better on the cloud servers.
Preview
Limit
A data warehouse query could potentially return huge amounts of data that could strain the UI.
For this reason, if you're not targeting specific data with a filter, use a limit in your queries, e.g. SELECT * FROM MyTable LIMIT 1000;
Updated 10 months ago