How to use query-data command of domo CLI

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.

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    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

     

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • 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

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • 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
    jaeW_at_Onyx Coach
    Answer ✓

    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

     

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • I was able to get the expected results.

     

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

This discussion has been closed.