insights/docs/published/handbook/engineering/data-warehouse.md

title, sidebar, showTitle

title	sidebar	showTitle
Working with data warehouse	Docs	true

This is an internal guide to setting up and working with the data warehouse for Insights engineers. If you're a Insights user, check out our data warehouse docs instead.

Adding a new source

Looking to add a new source to data warehouse? We have a detailed guide in the codebase.

If you're a customer of Insights Cloud and are looking to import data into your project, then you're likely looking for this section of the docs instead

Importing your local Postgres instance

1. Head to the new source flow in your local app, hit the link button next to Postgres
2. Use the following settings:
   1. host = 127.0.0.1
   2. port = 5432
   3. database = insights
   4. user = insights
   5. password = insights
   6. schema = public
3. Hit next, then select which tables you'd like to import. More info on the sync types can be found here. For the Postgres-specific xmin (cursorless incremental) sync type and its limitations, see the Postgres source README
4. Hit next and finish the import - temporal-worker-data-warehouse will then import the data into your local object storage

Accessing object storage

All your data warehouse data is stored in your local object storage (SeaweedFS, S3-compatible, running at http://localhost:19000). Unlike MinIO, SeaweedFS has no web console, so inspect it with any S3 client. For example, with the AWS CLI:

AWS_ACCESS_KEY_ID=object_storage_root_user AWS_SECRET_ACCESS_KEY=object_storage_root_password \
  aws --endpoint-url http://localhost:19000 s3 ls s3://data-warehouse/ --recursive

There's a separate folder under the data-warehouse bucket for each table you sync.

Setting up a MySQL source

If you want to set up a local MySQL database as a source for the data warehouse, there are a few extra set up steps you'll need to complete:

First, install MySQL:

brew install mysql
brew services start mysql

Once MySQL is installed, create a database and table, insert a row, and create a user who can connect to it:

mysql -u root

CREATE DATABASE insights_dw_test;
CREATE TABLE IF NOT EXISTS payments (id INT AUTO_INCREMENT PRIMARY KEY, timestamp DATETIME, distinct_id VARCHAR(255), amount DECIMAL(10,2));

INSERT INTO payments (timestamp, distinct_id, amount) VALUES (NOW(), '[email protected]', 99.99);

CREATE USER 'insights'@'%' IDENTIFIED BY 'insights';
GRANT ALL PRIVILEGES ON insights_dw_test.* TO 'insights'@'%';
FLUSH PRIVILEGES;

To verify everything is working as expected:

1. Navigate to "Data pipeline" in the Insights application.
2. Create a new MySQL source using the settings above (username and password both being insights)
3. Once the source is created, click on the "MySQL" item. In the schemas table, click on the triple dot menu and select the "Reload" option.

After the job runs, clicking on the synced table name should take you to your data.

Working with a MS SQL source

You'll need to install MS SQL drivers for the Insights app to connect to a MS SQL database. Learn the entire process in insights/warehouse/README.md. Without the drivers, you'll get the following error when connecting a SQL database to data warehouse:

symbol not found in flat namespace '_bcp_batch'