mapdql¶
Name¶
mapdql
Synopsis¶
mapdql [<database>]
[{--user|-u} <user>]
[{--passwd|-p} <password>]
[--port <port number>]
[{-s|--server} <server host>]
[--http]
Description¶
mapdql is the client-side SQL console that displays query results for SQL statements you submit
to the MapD Core Server.
Arguments¶
| Argument | Description |
|---|---|
| database | The database to connect to. Default = mapd. |
Options¶
| Option | Description |
|---|---|
--user | -u |
User name. Default = mapd. |
--passwd | -p |
User password. Default = HyperInteractive. |
--port |
Port number of MapD Core Server. Default = 9091. |
--server | -s |
MapD Core Server hostname in DNS name or IP address. Default = localhost. |
--http |
Use the Thrift HTTP transport instead of the default TCP
transport. Must set --port to the mapd_web_server port. Default = 9092. |
Running mapdql¶
After starting mapdql, you can enter SQL queries or backslash commands from the command line.
The MapD server has a default one hour timeout on individual HTTP requests, including those made from mapdql, when using Thrift HTTP transport. If your queries are expected to exceed the timeout, use either the default mapdql TCP transport or increase the timeout using the mapd_web_server --timeout option.
If the connection to the server is lost, mapdql automatically attempts to reconnect.
Commands¶
| Command | Description |
|---|---|
\h |
List available backslash commands. |
\u [<regex>] |
List users, with optional regular expression. |
\l |
List databases. |
\t [<regex>] |
List tables, with optional regular expression. |
\v [<regex>] |
List views, with optional regular expression. |
\d <table> |
Describe table columns using a SQL CREATE TABLE statement. |
\d <view> |
Describe the results of a view SELECT statement. |
\o <table> |
Return the optimal CREATE TABLE statement for a table, based on the size of the actual data stored. |
\c <database> <user> <password> |
Connect to a database. |
\gpu |
Switch to GPU mode in the current session. |
\cpu |
Switch to CPU mode in the current session. |
\timing |
Print timing information. |
\notiming |
Do not print timing information. |
\version |
Print MapD Core Server version. |
\memory_summary |
Print memory usage summary. |
\copy <file path> <table> |
Copy or append data from client-side file to table. The file is
assumed to be in CSV format unless the file name ends with
.tsv. |
\copygeo <file path> <table> |
Experimental support for copying a server side shapefile to a new table. |
\export_dashboard <dashboard name> <filename> |
Exports a dashboard to a filepath. Files with spaces in their names should be quoted. If there is a quote within a quoted string, it should be escaped with a backslash. |
\import_dashboard <dashboard name> <filename> |
Imports a dashboard from a filepath. Files with spaces in their names should be quoted. If there is a quote within a quoted string, it should be escaped with a backslash. |
\q |
Quit. |
Unlike SQL statements, backslash commands do not require a terminating semicolon character.
Runtime Examples¶
The \t, \u, and \v commands might return a long list of values. You can use a regular expression match pattern to filter the results. For example, you could use the following command to return only tables that start with the word flight.
mapdql> \t ^flight.*
flights_2008_10k
flights_2008_7M
SQL query example:
mapdql> SELECT * FROM movies WHERE movieId=260;
movieId|title|genres
260|Star Wars: Episode IV - A New Hope (1977)|Action|Adventure|Sci-Fi
Backslash command example that describes a table:
mapdql> \d movies
CREATE TABLE movies (
movieId INTEGER,
title TEXT ENCODING DICT(32),
genres TEXT ENCODING DICT(32))
If you frequently perform the same tasks, you can create a script and pipe it to mapdql. You can use both SQL commands and mapdql commands in your script.
cat script.sql | mapdql -p <password>
For example, if you periodically upload data to the movies table, you can append rows from files named movies.csv using the following script, and display the results.
\copy ./movies.csv movies
select * from movies;
When you pipe the script to mapdql, you get results similar to the following.
$ cat ~/script.sql | ./mapdql -p MyPasswordShhSecret
User mapd connected to database mapd
movieId|title|genres
1|Explosions Extravaganza|Action
2|Cuddle Time|Romantic Comedy
3|Chuckle Buddies|Comedy
4|All the Feels|Drama
User mapd disconnected from database mapd