DBWrite

Overview 

DBWrite functionality in Knowi allows datasets to be written into your destination. This will enable you to store Knowi Datasets back into your Datasource for posterity.  

Traditionally, data engineering requires the shipment of raw data into a warehouse (using Data Transporters like FiveTran), which are then joined up, cleaned, and transformed into another set of tables (using tools like dbt) that Businesses/Business Analysts can meaningfully use. This process results in an explosion of multiple tools, processes, complexity, and cost.  

Knowi provides an end-to-end simplified approach. Knowi Datasets allows you to query your raw Datastores, join against multiple sources, and perform transformations to provide curated datasets that business users can use immediately. Furthermore, the DBWrite feature enables you to store the results in your store so that your datasets are easily accessible by users of Knowi and your downstream systems. 

Knowi provides three Data Strategy options that you can pick and choose as needed: 

1. Direct: This can be Direct queries, Direct Queries with Runtime Parameters. Optionally, caching can be enabled with a TTL on queries with the same fingerprint for faster query executions. 
2. ElasticStore: Execute queries and store the results in Knowi ElasticStore. This can be a ‘Run Once’ query or a scheduled query with various powerful overwrite strategies (Append, key-based upsert, TTL, etc).  
3. DBWrite: Same as #2, except it can be written into your store. 

NOTE 1: Currently, Knowi provides support for Amazon Redshift, Snowflake, MySQL, MongoDB, BigQuery, and PostgreSQL as a write-back destination, with others coming soon. 

NOTE 2: Contact your Account manager or our support to enable DBWrite. 

Define DBWrite Destination

You can just navigate to the Datasources listing and edit the Database you want to set as a write-back destination. You can also create a new Datasource and use it as the write-back destination. 

Check the option- Writable Destination and click on the Save button. 

Enable Custom Store

Query a Datasource of your choice and define the Data Execution Strategy. 

By default, Knowi will write the query results into Knowi’s ElasticStore. To enable your own DBWrite destination, choose Run Once Scheduled Intervals or Triggered Query.

Check the option- Use Custom Store and select the Database (set as DBWrite destination) from the dropdown.

Based on the datasource selected, other fields will populate. Since the example uses Amazon Redshift, you can configure Schema (Optional) and enter the name for your new table that you want to write back to the selected Database. 

You can preview the results and click the Create and Run button to add the result to the table. 

Once complete, the table with data will now exist in your Database. 

Now, you can create a visualization, configure the visualization settings, set up a widget, and add the widget to a dashboard. For more information, please see Create a Visualization on Dataset.

You can navigate to a dashboard to load and view the data in your table. 

Please note that, _knowi_ts is an automatically generated column with a timestamp of when the particular row was added. 

Support for SUPER Type

Knowi supports the SUPER datatype that can be used to store semistructured data or documents as values in Redshift. Read more about SUPER datatype here.