Using the SQL CLI
Use the SQL CLI to validate your analytics configuration before moving on to other analytics tools. The CLI is a fully-featured interactive SQL environment in its own right.
Note Presto has been renamed to Trino.
Installing the CLI
- Download the Presto CLI
- Rename the jar to
presto
chmod +x presto
to make the jar executable.
Using the CLI
You will typically launch the SQL CLI with three arguments:
- The required
--server
argument specifies the analytics server address. Because you are connecting through the localdatomic access
proxy this will belocalhost:8989
. - The optional
--catalog
argument must match a catalog configured for your system. - The optional
--schema
argument must be the name of a Datomic database.
The catalog
and schema
arguments are defaults for queries that do
not explicitly name a catalog and schema, so that in your queries you
can use table names directly, e.g. country
instead of
tomhanks.mbrainz.country
.
./presto --server localhost:8989 --catalog <my-catalog> --schema <db-name>
You should be presented with a SQL prompt:
presto>
Validating Configuration
The examples below presume a Datomic system named tomhanks
with a
database named mbrainz
, and a SQL CLI launched with:
./presto --server localhost:8989 --catalog tomhanks --schema mbrainz
Catalogs
The following query shows all catalogs.
select * from system.metadata.catalogs;
=> catalog_name | connector_id --------------+-------------- analytics | analytics sample | sample system | system catalog_name | connector_id
You should see a system
catalog, plus one entry per catalog properties file. In this example above, the
Datomic catalog name is tomhanks
.
Show Schemas
The following query shows all the schemas in the default catalog:
show schemas;
=> Schema -------------------- information_schema mbrainz
You should see an information_schema
catalog, plus one schema per
exposed Datomic database. In the example above, the tomhanks
system
has a single database named mbrainz
.
Show Tables
The following query shows all the tables in the default schema.
show tables;
=> Table --------------------------- abstractrelease abstractrelease_x_artists artist artist_gender artist_type country db__attrs db__idents (continues...)
You should see db__attrs
and db__idents
, plus any tables you have
configured via a metaschema.
Describe a Table
The following query describes a table named country
in the default schema:
describe country;
=> Column | Type | Extra | Comment --------+---------+-------+--------- code | varchar | | name | varchar | | db__id | bigint | |
Query
select * from track_x_artists limit 10;
select * from country limit 10;
This is an opportune time to view your Dashboard.
More Things To Try
Try some of the system commands to see what is present in the system.
You can access more than one database (or even more than one catalog)
by using fully qualified table names in you SQL,
e.g. [catalog].[database].[table]
. You can also write queries that
join across different databases or catalogs.