Data Warehouse Integration¶

Part of your data might live in databases requiring some level of integration with your warehouse to retrieve and inject them into Pachyderm.

Our SQL ingest tool provides a seamless connection between databases and Pachyderm, allowing you to import data from a SQL database into Pachyderm-powered pipelines. By bringing data-driven pipelines, versioning & lineage to structured data, we are allowing Data Science teams to easily combine structured and unstructured data.

Specifically, we help you connect to a remote database of your choice and pull the result of a given query at regular intervals in the form of a CSV or a JSON file.

Use SQL Ingest¶

Pachyderm's SQL Ingest uses jsonnet pipeline specs with the following parameters to automatically create the pipelines that access, query, and materialize the results of a SQL query to a data warehouse. The outputted results can take the form of CSV or JSON files. Check the Formatting section at the bottom of the page for specific details on formats and SQL Datatypes.

Pass in the following parameters and get your results committed to an output repo, ready for the following downstream pipeline:

pachctl update pipeline --jsonnet https://raw.githubusercontent.com/pachyderm/pachyderm/2.2.x/src/templates/sql_ingest_cron.jsonnet \
  --arg name=myingest \
  --arg url="mysql://root@mysql:3306/test_db" \
  --arg query="SELECT * FROM test_data" \
  --arg cronSpec="@every 30s" \
  --arg secretName="mysql-creds" \
  --arg format=json 

Where the parameters passed to the jsonnet pipeline spec are:

When the command is run, the database will be queried on a schedule defined in your cronSpec parameter and a result file committed to the output repo named after name.

Database Secret¶

Before you create your SQL Ingest pipelines, make sure to create a generic secret containing your database password in the field PACHYDERM_SQL_PASSWORD.

apiVersion: v1
kind: Secret
metadata:
  name: mysql-creds
data:
  "PACHYDERM_SQL_PASSWORD": "cm9vdA==" # base64 encoded

Database Connection URL¶

Pachyderm's SQL Ingest will take an URL as its connection string to the database of your choice.

The URL is structured as follows:

<protocol>://<username>@<host>:<port>/<database>?<param1>=<value1>&<param2>=<value2>

Where:

How Does This Work?¶

SQL Ingest's jsonnet pipeline specs sql_ingest_cron.jsonnet creates two pipelines:

• A Cron Pipeline myingest_queries triggering at an interval set by cronSpec and outputting a file /0000 in its output repo myingest_queries. /0000 contains a timestamp and the SQL statement set in query.
• The following pipeline myingest takes the /0000 file as input and runs the query against the database set in url. The query's result is then materialized in a file (JSON or CSV) of the same name /0000 committed to the output repo myingest.

The same base image pachctf is used in both pipelines.

Check the visual representation of the SQL Ingest DAG created above in Console:

In your terminal:

• The list of the DAG's pipelines (pachctl list pipeline) looks like this:

  

• 3 repos are created:

  

How To Inspect The Result Of A Query?¶

You have run a query using SQL Ingest. How do you inspect its result?

• Check what the query looked like:

  pachctl get file myingest_queries@master:/0000
  

  -- 1643235475
  SELECT * FROM test_data
  

• Read the file written to the output repo myingest:

  pachctl list file myingest@master
  

  NAME  TYPE SIZE
  /0000 file 52B
  

  pachctl get file myingest@master:/0000
  

  {"mycolumn":"hello world","id":1}
  {"mycolumn":"hello you","id":2}
  

Formats and SQL DataTypes¶

The following comments on formatting reflect the state of this release and are subject to change.

SQL datatypes supported¶

We support the following SQL datatypes. Some of those Data Types are specific to a database.

Formatting¶

• All numeric values are converted into strings in your CSV and JSON.

  Warning

  • Note that infinite (Inf) and not a number (NaN) values will also be stored as strings in JSON files.
  • Use this format #.# for all decimals that you plan to egress back to a database.

  Examples

  Database	CSV	JSON
  12345	12345	"12345"
  123.45	123.45	"123.45"

• Date/Timestamps

  Examples

  Type	Database	CSV	JSON
  Date	2022-05-09	2022-05-09T00:00:00	"2022-05-09T00:00:00"
  Timestamp ntz	2022-05-09 16:43:00	2022-05-09T16:43:00	"2022-05-09T16:43:00"
  Timestamp tz	2022-05-09 16:43:00-05:00	2022-05-09T16:43:00-05:00	"2022-05-09T16:43:00-05:00"

• Strings

  Keep in mind when parsing your CSVs in your user code that we escape " with "" in CSV files.

  Examples

  Database	CSV
  "null"	null
  `""`	""""""
  ""	""
  nil
  "my string"	"""my string"""
  "this will be enclosed in quotes because it has a ,"	"this will be enclosed in quotes because it has a ,"