Run SQL Server in Docker and query with DataStation
This tutorial walks through running SQL Server in Docker, creating a table and loading it with data from a CSV, and finally querying the database in DataStation.
Database initialization [Optional]
If you want to follow along with this tutorial verbatim, in your terminal start SQL Server in Docker:
$ cid=$(docker run -d -p 1433:1433 -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=yourStrong(%)Password" mcr.microsoft.com/mssql/server)
Now create a table for some address data (it may take a few retries to run this command while SQL Server is initializing).
$ docker exec -t $cid /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "yourStrong(%)Password" 1> CREATE DATABASE test 2> GO 1> USE test 2> GO 1> CREATE TABLE addresses (id BIGINT, location_id BIGINT, address_1 VARCHAR(MAX), address_2 VARCHAR(MAX), city VARCHAR(MAX), state_province VARCHAR(MAX), postal_code VARCHAR(MAX), country VARCHAR(MAX)) 2> GO
Then download a CSV of sample addresses from Github, copy it into the Docker container, and load the addresses into this new table.
$ curl -LO https://raw.githubusercontent.com/codeforamerica/ohana-api/master/data/sample-csv/addresses.csv $ docker cp ./addresses.csv $cid:/tmp/addresses.csv $ docker exec -t $cid /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "yourStrong(%)Password" 1> USE test 2> GO 1> BULK INSERT addresses FROM '/tmp/addresses.csv' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK) 2> GO (21 rows affected)
And we're done!
Data source setup
Now inside DataStation create a new data source in the left sidebar.
Give it a nice name so you can easily find it later. And select SQL Server in the Vendor dropdown.
If you are connecting to
localhost:1433 (the default), you can
leave the host field blank. If your port is 1433 then you can always
omit the colon and port and just specify the address.
Next we set the database to
test, the username to
SA, and the
yourStrong(%)Password (we specified this when we started
the Docker container).
SSH Proxy [Optional]
If you want connect to a SQL Server database on a remote server through an SSH proxy, you will need to set up the SSH connection to that server first. Once you do, you can select that connection in the "Via Server" dropdown within the data source editor.
Now create a new panel and select the Database type.
Run a query
Enter your query and hit play!
You can always download the results of a panel by hitting the download button. Or you can reference the results in other panels.
After running the query, a table button will appear below the panel next to the New Panel button. Click it to generate a table based on this panel.
About this page
See an error or want to add a clarification? This page is
generated from this file on Github.
Last edited Apr 06, 2022.