CLI to write data and query using SQL

[hiltontj]

Provide new subcommands to send queries and writes to the server.

Proposal

Us a type parameter in the query to signal the the protocal in which we are querying/writing, e.g., SQL vs. InfluxQL, or line protocol vs. v3 line protocol. Following is how this would look for the query and write interfaces, respectively.

query Sub-command

Simplified, i.e., relying on defaults/environment:

influxdb3 query "SELECT * FROM bar"

Fully specified:

influxdb3 query --type sql --host localhost:8181 --db foo --token abc123 "SELECT * FROM bar"

• type: sql (default), influxql
• host: server host address (default: 127.0.0.1:8181, or from env)
• db: target database (default from env INFLUXDB3_DATABASE_NAME)
• token: Bearer token for auth (default from env INFLUXDB3_AUTH_TOKEN)
• The query string is taken as the final argument.

write Sub-command

Simplified:

influxdb3 write "bar,a=b field=val 123456"

Fully specified:

influxdb3 write --type lp --host localhost:8181 --db foo --token abc123 "bar,a=b field=val 123456"

• type: lp (default)
• others same as query

Alternatives

1. Dedicated sub-command for each underlying query path, e.g., supporting SQL and InfluxQL would then correspond to the following:

   influxdb3 query_sql "SELECT * FROM foo"
   

   and

   influxdb3 query_influxql "SELECT * FROM foo"
   

   respectively.

2. A Named parameter for accepting the query string, e.g.,

   influxdb3 query --sql "SELECT * FROM foo"
   

   influxdb3 query --influxql "SELECT * FROM foo"
   

Other Notes

The propsed solution, as well as (2.) would allow us to alias the subcommand, e.g.,

influxdb3 q "SELECT * FROM foo"

influxdb3 w "bar,a=b field=val 123456"

[alamb]

FWIW a similar command already exists in the infuxdb_iox binary:

influxdb_iox query <namespace> <sql_string>
influxdb_iox write  <namespace> <filename>

So you can probably take a friendly look at how that works

[pauldix]

I think I prefer alternative 1 as that maps to the API (/api/v3/query_sql and /api/v2/query_influxql). All the other stuff looks good to me.

[hiltontj]

I think I prefer alternative 1 as that maps to the API (/api/v3/query_sql and /api/v2/query_influxql). All the other stuff looks good to me.

Although (1.) requires a little more typing than the original proposal, I do appreciate that it forces explicit intent. Also, (1.) does not prevent us from adding shorter aliases should we choose.

[pauldix]

Ah right, shortcutting it. I skipped over your "Other Notes". +1 for the q and w shortcuts 😄

[hiltontj]

I have spent some time reviewing the IOx query/write sub-commands and have come away with the following:

• We will need to implement an influxdb3_client, which I have created an issue for here #24661
• There is a single query sub-command, which accepts a lang parameter to determine SQL vs. InfluxQL, and then uses Flight under the hood regardless of which is specified
• The query sub-command needs to accept a parameter that specifies output format, I missed that in original issue description
• The write sub-command accepts a file instead of the line protocol directly, I like that approach and think we should be consistent with iox there

Eventually, I would prefer to have a single query sub-command, to be consistent with IOx, but perhaps for now we can have dedicated query_sql and query_influxql sub-commands that map direct to the underlying API. Once #24643 is done we can re-consider.

[mgattozzi]

It's worth noting @hiltontj that the output format will default to JSON for now and eventually JSON Lines. The format parameter is optional. I think we should do query with a flag to specify the string type with a default of SQL. Same with write but with the lp for now

[hiltontj]

For querying: after some discussion, we decided to go with a single query sub-command, similar to the original proposal above. This provides a single point of access, and allows us to dictate the default language that we want users to use, i.e., SQL. Instead of type, the param to specify language can be lang.