Server usage
This guide will teach you how to set up and use a SQLpipe server.
- You must have completed the SQLpipe installation guide.
- You must have a PostgreSQL database for SQLpipe to use as a backend.
- Optionally, you can prepare two supported systems to transfer data between.
If you are not sure how to set up a PostgreSQL database, there are many tutorials on the internet, such as this one: Set up a PostgreSQL DB locally with Docker.
A SQLpipe server uses PostgreSQL for:
- User authentication
- Storing connection data
- Recording transfer and query results
Below we show how to create a new DB and intialize it for SQLpipe.
Connect to PostgreSQL and create a fresh database. You can call it whatever you want.
CREATE DATABASE sqlpipe;
SQLpipe's
initialize
command creates the necessary schema which a SQLpipe server needs to run.sqlpipe initialize --dsn="postgres://<username>:<password>@<hostname>/<db-name>"
You can optionally skip confirmation by passing the
--force
flag.Next, we need to start the server, and create an admin user that is able to log in to system.
Starting a SQLpipe server is accomplished with the
serve
command. If you pass the --create-admin
flag, as well as --admin-username
and --admin-password
, it will also create a user with admin privileges.Use the following command as a template:
sqlpipe serve \
--create-admin \
--admin-username=<desired-admin-username> \
--admin-password=<desired-admin-password> \
--dsn="postgres://<username>:<password>@<hostname>/<db-name>"
You can view example usage of the
serve
command on the CLI reference page.If the server starts succesfully, the terminal will print messages like this:
{"level":"INFO","time":"2022-02-10T10:14:45Z","message":"database connection pool established"}
{"level":"INFO","time":"2022-02-10T10:14:45Z","message":"successfully created admin user"}
{"level":"INFO","time":"2022-02-10T10:14:45Z","message":"starting server","properties":{"addr":":9000"}}
SQLpipe's default port is 9000. You can change that port with the
serve
command's --port
flag.Type the admin username and password that you passed to the
serve
command to login.If login is successful, you will see the transfers screen.
One of SQLpipe's supported systems is PostgreSQL, which is the database SQLpipe uses as a backend. This means we have two options for running our first transfer, we can either:
- Transfer data between two data systems that we have previously prepared.
- Transfer data from our PostgreSQL backend, through SQLpipe, then back to our PostgreSQL backend.
Transferring data from SQLpipe's backend, to SQLpipe's backend, is not very useful, but it is an easy way to get started. And so, that is what this guide will show you how to do.
However, feel free to add two connections of your own, and transfer between those systems, instead of following along with this contrived example.
Navigate to the Connections page, and click on the blue plus button to create a new connection.
You will be greeted by a form asking for connection information:
A few notes on these fields:
- Name can be anything you want, but must be unique.
- Hostname and Port are required, unless Data system type is Snowflake. If it is Snowflake, then Account ID is required.
- Skip connection test will skip SQLpipe's automatic check to see if it can connect with the given information.
If you want to transfer from SQLpipe's backend, to SQLpipe's backend, and are using a local PostgreSQL DB, enter the following information:
- Name:
My First Connection
- Data system type:
PostgreSQL
- Hostname:
localhost
- Port:
5432
- DB Name: The name of the PostgreSQL DB that you created previously
- Username: Your PostgreSQL username
- Password: Your PostgreSQL user's password
- Do not check the "Skip connection test" box
Otherwise, enter the data of your own data systems. My form looks like this:
Submit the form, and if everything works, you will get a success message.
Time to get to the good stuff!
Navigate to the transfers page, and click the blue plus button to create a transfer.
You will be greeted by another form:
An explanation of the fields:
- Source is the connection that SQLpipe will extract data from
- Target is the connection SQLpipe will insert into
- Target schema is the schema that SQLpipe will insert into.
- This field is only required on systems that have schema support.
- PostgreSQL supports schemas. It's default schema is
public
.
- Target table is the table SQLpipe will insert into
- Query is the query that will run on the source system.
- Overwrite will drop the target table (if it exists) and create a new table with:
- The same column names as the query result
- Column types that will accept the query result's data
One of the tables that SQLpipe uses to operate is
connections
. If you were able to add the backend DB connection, there should be a record in it. So, let's transfer the connections
table to another table. Here is what my form looks like:After submitting the form, you will come to a screen showing the transfer's details. Notice how the status is
queued
. All transfers and queries submitted to a SQLpipe server start as queued
, before eventually going active
(when the server has resources to spare), and then hopefully being marked as complete
.However, if a query or transfer encounters an error, the status will be marked as
error
, and the error will show on the page.Also, transfers can be cancelled with the red X button in the top right of the transfer details page. This will mark the transfer's status as
cancelled
.Refresh the page, and the transfer's status should be
complete
by now:Time for the moment of truth - did the data really transfer?
Login to your target database and look at the target table that you specified.
In my case, the target table was
my_new_table
, and it should show the connection I just added.sqlpipe=# select * from my_new_table;
id | created_at | name | ds_type | username | password | account_id | hostname | port | db_name | version
----+------------------------+---------------------+------------+----------+-----------+------------+-----------+------+---------+---------
1 | 2022-02-10 10:56:43+00 | My First Connection | postgresql | postgres | NotTellin | | localhost | 5432 | sqlpipe | 1
(1 row)
And there it is!
You can also use SQLpipe's API to trigger transfers - this makes it a great solution for triggering transfers with a workflow automation tool like Airflow, or anything that can send a POST request.
SQLpipe's API is quite flexible - anything you can do on the UI can be done via the API.
- Transfers
- Queries
- Connections
- Users
All routes are listed on our API reference page.
Let's create another transfer via the API. We must send a
POST
request with the following fields:sourceID int
targetID int
query string
targetSchema string
targetTable string
overwrite bool
To the following route:
/api/v1/transfers
We can use curl to send this request:
curl -k -i \
-u <your-sqlpipe-admin-username>:<your-sqlpipe-password> \
-d '{"sourceId": 1, "targetId": 1, "overwrite": true, "targetSchema": "public", "targetTable": "my_second_table", "query": "select * from connections"}' \
https://localhost:9000/api/v1/transfers
If everything goes OK, you will receive a response like this:
HTTP/2 202
content-type: application/json
content-length: 347
date: Thu, 10 Feb 2022 12:09:55 GMT
{
"transfer": {
"id": 2,
"createdAt": "2022-02-10T12:09:55Z",
"sourceID": 1,
"targetID": 1,
"query": "select * from connections",
"targetSchema": "public",
"targetTable": "my_second_table",
"overwrite": true,
"status": "queued",
"error": "",
"errorProperties": "",
"stoppedAt": "0001-01-01T00:00:00Z",
"version": 1
}
}
And the SQLpipe server logs will show that it received your request:
{"level":"INFO","time":"2022-02-10T12:09:54Z","message":"Request received","properties":{"Method":"POST","Protocol":"HTTP/2.0","Remote address":"[::1]:60504","Requested address":"/api/v1/transfers"}}
{"level":"INFO","time":"2022-02-10T12:09:55Z","message":"now running a transfer","properties":{"CreatedAt":"10 Feb 2022 12:09:55 UTC","ID":"2","Overwrite":"true","Query":"select * from connections","SourceID":"0","Status":"active","TargetID":"0","TargetSchema":"public","TargetTable":"my_second_table"}}
Hopefully you are finding that SQLpipe is easy to use, and somewhat self explanatory.
Last modified 10mo ago