How to use query-data command of domo CLI

„É¶„Éº„Ç∂„Éº29657 · June 2020

Hello all.

When using the domoCLI query-data command, there are --query and --queryfile command options.
I would like to know how to write a JSON-formatted query that can be used for each.

For example, what if you want to retrieve only the "Name" field from the following dataset.

[Example data set]
　ID Name　 Address
　1　John　　 Minnesota
　2　Robert　 Wisconsin
　3　William　Michigan
　4　James　 New Jersey
　5　Charles　California

That's all. Thank you.

jaeW_at_Onyx · June 2020

make sure to use the help command...

query-data -i <dataset_id> -xf <export_filename> -sql "SELECT AVG(avgHeartRate) AS heartRate, activityName FROM `{dataset_id}` WHERE avgHeartRate > 0 GROUP BY athleteId, month(startDate), year(startDate)"

usage:
-i,--id <ID> dataset id
-q,--query <QUERY> query
-qf,--queryfile <QUERYFILE> query filename
-sf,--sqlfile <SQLFILE> sql filename
-sql,--sql <SQL> sql query
-xf,--exportfile <EXPORTNAME> export filename

looks like a SQL query would be -sql not -q.

I would put my SELECT statement into a csv file (myquery.sql) and then use the -sf flag

jaeW_at_Onyx · June 2020

QueryFile will definitely be the easier b/c you don't have to worry about escaping single ticks ' in your SELECT statement.

Try

SELECT name FROM dataset_id

„É¶„Éº„Ç∂„Éº29657 · June 2020

Thank you for your specific advice!

I tried the following command.

[Executed Command]
query-data -xf "C:test\export.csv" -i
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx -q SELECT Name FROM xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

And the execution result is as follows.

[Result]
Unrecognized token 'SELECT': was expecting ('true', 'false' or 'null')
at [Source: (String)"SELECT"; line: 1, column: 13]

Do you know what caused it?

I tried command variously, but it was the same.

For example...

query-data -xf "C:test\export.csv" -i
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx -q SELECT Name FROM xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx;

query-data -xf "C:test\export.csv" -i
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx -q "SELECT Name FROM xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

query-data -xf "C:test\export.csv" -i
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx -q "SELECT Name FROM xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx;"

jaeW_at_Onyx · June 2020

make sure to use the help command...

query-data -i <dataset_id> -xf <export_filename> -sql "SELECT AVG(avgHeartRate) AS heartRate, activityName FROM `{dataset_id}` WHERE avgHeartRate > 0 GROUP BY athleteId, month(startDate), year(startDate)"

usage:
-i,--id <ID> dataset id
-q,--query <QUERY> query
-qf,--queryfile <QUERYFILE> query filename
-sf,--sqlfile <SQLFILE> sql filename
-sql,--sql <SQL> sql query
-xf,--exportfile <EXPORTNAME> export filename

looks like a SQL query would be -sql not -q.

I would put my SELECT statement into a csv file (myquery.sql) and then use the -sf flag

„É¶„Éº„Ç∂„Éº29657 · June 2020

I was able to get the expected results.

I was making a misunderstanding.
Thank you for your advice.

How to use query-data command of domo CLI

Best Answer

Answers

Categories