How to use query-data command of domo CLI

ユーザー29657

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

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

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

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

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

I was able to get the expected results.

 

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