Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://datastation.multiprocess.io/docs/tutorials/Query_MySQL_with_DataStation.html below:

Run MySQL in Docker and query with DataStation

This tutorial walks through running MySQL in Docker, creating a table and loading it with data from a CSV, and finally querying the database in DataStation.

If you want to follow along with this tutorial verbatim, in your terminal start MySQL in Docker:

$ cid=$(docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql)

Now create a table for some address data (it may take a few retries to run this command while MySQL is initializing).

$ docker exec $cid mysql -uroot -proot mysql --execute 'CREATE TABLE addresses (id BIGINT, location_id BIGINT, address_1 TEXT, address_2 TEXT, city TEXT, state_province TEXT, postal_code TEXT, country TEXT);'
mysql: [Warning] Using a password on the command line interface can be insecure.

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 $cid mysql --local-infile=1 -uroot -proot mysql --execute "SET GLOBAL local_infile=1; LOAD DATA LOCAL INFILE '/tmp/addresses.csv' INTO TABLE addresses FIELDS TERMINATED BY ',' ENCLOSED BY '""' IGNORE 1 ROWS;"
mysql: [Warning] Using a password on the command line interface can be insecure.

And we're done!

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 MySQL in the Vendor dropdown.

If you are connecting to localhost:3306 (the default), you can leave the host field blank. If your port is 3306 then you can always omit the colon and port and just specify the address.

Next we set the database to mysql, the username to root, and the password to root (we specified this when we started the Docker container).

If you want connect to a MySQL 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.

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.

See an error or want to add a clarification? This page is generated from this file on Github.

Last edited Apr 06, 2022.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4