bugGNU Parallel - Bugs: bug #62523, Support influxdb in 'sql'

 
 

bug #62523: Support influxdb in 'sql'

Submitter:  Jesse Becker <hawson>
Submitted:  Wed 25 May 2022 02:25:37 PM UTC
   
 
Category:  None Severity:  3 - Normal
Item Group:  None Status:  Fixed
Privacy:  Public Assigned to:  tange
Open/Closed:  Closed
* Mandatory Fields

Add a New Comment Rich Markup
   

Mon 05 Sep 2022 08:06:37 PM UTC, comment #3: 

This is by design:

    commands

    The SQL commands to run. Each argument will have a newline appended.

    Example: "SELECT FROM foo;" "SELECT FROM bar;"

This design is due to Oracle:

       Oracle requires newlines after each statement. This can be done like this:

       sql :myora "SELECT FROM foo;" "SELECT FROM bar;"

Ole Tange <tange>
Group administrator
Tue 12 Jul 2022 03:28:19 PM UTC, comment #2: 


> Please try [d4be5907b9].


Looks like there is an issue with quoting.  Specifically, it appears that the command string passed on the CLI is split into separate commands sent in sequence.

(output edited for brevity/security).

Compare these two unquoted/quoted commands:

$ PATH=/usr/bin ./sql -D   :telegraf show measurements 2> /dev/null | sed 's/-host.*-database//' | grep execute
[/usr/bin/influx       telegraf -execute show]
[/usr/bin/influx       telegraf -execute measurements]

$ PATH=/usr/bin ./sql -D   :telegraf 'show measurements' 2> /dev/null | sed 's/-host.*-database//' | grep execute
[/usr/bin/influx       telegraf -execute show measurements]


A longer example:

$ PATH=/usr/bin ./sql -D :telegraf select usage_user  from cpu where tg_host='myhost' and 'time>now()-10m' group by 'time(1m)'    2> /dev/null | sed 's/-host.*-database//' | grep execute
[/home/beckerje/bin/influx       telegraf -execute select]
[/home/beckerje/bin/influx       telegraf -execute usage_user]
[/home/beckerje/bin/influx       telegraf -execute from]
[/home/beckerje/bin/influx       telegraf -execute cpu]
[/home/beckerje/bin/influx       telegraf -execute where]
[/home/beckerje/bin/influx       telegraf -execute tg_host=myhost]
[/home/beckerje/bin/influx       telegraf -execute and]
[/home/beckerje/bin/influx       telegraf -execute time>now()-10m]
[/home/beckerje/bin/influx       telegraf -execute group]
[/home/beckerje/bin/influx       telegraf -execute by]
[/home/beckerje/bin/influx       telegraf -execute time(1m)]


My original patch does not require quoting, nor, I think, do any other DB implementations (which I confirmed against local mysql and psql databases).

Jesse Becker <hawson>
Thu 07 Jul 2022 09:38:07 AM UTC, comment #1: 

Please try [d4be5907b9].

Ole Tange <tange>
Group administrator
Wed 25 May 2022 02:25:37 PM UTC, original submission:  

A small patch to add support for InfluxDB to the 'sql' program.

Happy to re-work as needed.


diff --git a/src/sql b/src/sql
index 2781e4fb..fec9fdb3 100755
--- a/src/sql
+++ b/src/sql
@@ -161,6 +161,23 @@ retry the command. Default is I<--retries 1>.
 =item B<-s> I<string>

 Field separator. Use I<string> as separator between columns.
+(ignored for with InfluxDB databases)
+
+=item B<--csv>
+
+Request CSV output.  InfluxDB only.
+
+
+=item B<--json>
+
+=item B<--pretty>
+
+Request (pretty) JSON output; --pretty implies --json.  InfluxDB only.
+
+
+=item B<--precision> <I<rfc3339|h|m|s|ms|u|ns>>
+
+Specifiy the format of the output timestamps: rfc3339, h, m, s, ms, u or ns.


 =item B<--skip-first-line>
@@ -222,12 +239,14 @@ Examples:
  postgresqlssl://scott@pg.example.com:3333/pgdb
  sql:sqlite2:////tmp/db.sqlite?SELECT * FROM foo;
  sqlite3:///../db.sqlite3?SELECT%20*%20FROM%20foo;
+ influxdb://scott:tiger@influxdb.example.com/telegraf

 Currently supported vendors: MySQL (mysql), MySQL with SSL (mysqls,
 mysqlssl), Oracle (oracle, ora), PostgreSQL (postgresql, pg, pgsql,
 postgres), PostgreSQL with SSL (postgresqlssl, pgs, pgsqlssl,
 postgresssl, pgssl, postgresqls, pgsqls, postgress), SQLite2 (sqlite,
-sqlite2), SQLite3 (sqlite3).
+sqlite2), SQLite3 (sqlite3), InfluxDB 1.x (influx, influxdb), InfluxDB
+with SSL (influxdbssl, influxdbs, influxs, influxssl)

 Aliases must start with ':' and are read from
 /etc/sql/aliases and ~/.sql/aliases. The user's own
@@ -491,7 +510,7 @@ have a command history for Oracle.

 =head1 SEE ALSO

-B<mysql>(1), B<psql>(1), B<rlwrap>(1), B<sqlite>(1), B<sqlite3>(1), B<sqlplus>(1)
+B<mysql>(1), B<psql>(1), B<rlwrap>(1), B<sqlite>(1), B<sqlite3>(1), B<sqlplus>(1), B<influx>(1)

 =cut

@@ -524,6 +543,9 @@ if($database_driver eq "mysql" or
 } elsif($database_driver eq "sqlite" or
         $database_driver eq "sqlite3") {
     ($batch_command,$interactive_command) = sqlite_commands($database_driver,%dburl);
+} elsif($database_driver eq "influx" or
+        $database_driver eq "influxdb") {
+    ($batch_command,$interactive_command) = influxdb_commands($database_driver,%dburl);
 }

 my $err;
@@ -564,7 +586,9 @@ if($dburl{'query'}) {
 do {
     if (is_stdin_terminal()) {
         if(@ARGV) {
-            open(M,"| $batch_command") || die("mysql/psql/sqlplus not in path");
+            $::opt_debug and print "[ | $interactive_command]\n";
+            $::opt_verbose and print "[ | $interactive_command]\n";
+            open(M,"| $batch_command") || die("mysql/psql/sqlplus/influx not in path");
             for(@ARGV) {
                 s/\\n/\n/g;
                 s/\\x0a/\n/gi;
@@ -572,13 +596,13 @@ do {
             }
             close M;
         } else {
-            $::opt_debug and print "$interactive_command\n";
-            $::opt_verbose and print "$interactive_command\n";
+            $::opt_debug and print "[$interactive_command]\n";
+            $::opt_verbose and print "[$interactive_command]\n";
             system("$interactive_command");
         }
     } else {
         if(@ARGV) {
-            open(M,"| $batch_command") || die("mysql/psql/sqlplus not in path");
+            open(M,"| $batch_command") || die("mysql/psql/sqlplus/influx not in path");
             for(@ARGV) {
                 s/\\n/\n/g;
                 s/\\x0a/\n/gi;
@@ -586,8 +610,8 @@ do {
             }
             close M;
         } else {
-            $::opt_debug and print "$batch_command\n";
-            $::opt_verbose and print "$batch_command\n";
+            $::opt_debug and print "[$batch_command]\n";
+            $::opt_verbose and print "[$batch_command]\n";
             system("$batch_command");
         }
     }
@@ -617,9 +641,9 @@ sub parse_options {
     GetOptions("passthrough|p=s@" => \$::opt_p,
                "sep|s=s" => \$::opt_s,
                "html" => \$::opt_html,
-               "show-processlist|proclist|listproc" => \$::opt_processlist,
-               "show-tables|showtables|listtables|list-tables|tablelist|table-list"
-               => \$::opt_tablelist,
+               "show-processlist|proclist|listproc|showqueries|show-queries" => \$::opt_processlist,
+               "show-tables|showtables|listtables|list-tables|tablelist|table-list".
+               "show-measurements|list-measurments|showmeasurements|listmeasurments" => \$::opt_tablelist,
                "dblist|".
                "listdb|listdbs|list-db|list-dbs|list-database|".
                "list-databases|listdatabases|listdatabase|showdb|".
@@ -631,6 +655,11 @@ sub parse_options {
                "r" => \$::opt_retry,
                "retries=s" => \$::opt_retries,
                "debug|D" => \$::opt_debug,
+               # Influxdb only
+               "json|j" => \$::opt_json,
+               "pretty" => \$::opt_pretty,
+               "csv" => \$::opt_csv,
+               "precision=s" => \$::opt_precision,
                # Shebang #!/usr/bin/parallel -Yotheroptions
                "Y|shebang" => \$::opt_shebang,
                "skip-first-line" => \$::opt_skip_first_line,
@@ -671,6 +700,8 @@ sub database_driver_alias {
                                  "sqlite" => "sqlite",
                                  "sqlite2" => "sqlite",
                                  "sqlite3" => "sqlite3",
+                                 "influx" => "influxdb",
+                                 "influxdb" => "influxdb",
         );
     return $database_driver_alias{$driver};
 }
@@ -801,6 +832,33 @@ sub sqlite_commands {
     return($batch_command,$interactive_command);
 }

+sub influxdb_commands {
+    my ($database_driver,%opt) = (@_);
+    find_command_in_path("influx") || die ("influx not in path");
+    if(defined($::opt_s)) { die "Field separator not implemented for influx" }
+    my $password = defined($opt{'password'}) ? "-password=".$opt{'password'} : "";
+    my $host = defined($opt{'host'}) ? "-host=".$opt{'host'} : "";
+    my $port = defined($opt{'port'}) ? "-port=".$opt{'port'} : "";
+    my $user = defined($opt{'user'}) ? "-username=".$opt{'user'} : "";
+    my $database = defined($opt{'database'}) ? "-database $opt{'database'}" : $ENV{'USER'};
+
+    my $format = defined($::opt_json) ? "-format json" :
+                 defined($::opt_pretty) ? "-format json -pretty" :
+                 defined($::opt_csv) ? "-format csv" : "";
+    my $precision = defined($::opt_precision) ? "-precision $::opt_precision" : "";
+
+    my $no_headers = defined($::opt_n) ? "--skip-column-names" : "";
+    my $ssl = "";
+    if ($database_driver eq "influxsql" or $database_driver eq "influxdbsql") {
+        $ssl="--ssl";
+    }
+
+    $batch_command =
+        "/usr/bin/influx  $pass_through_options $no_headers $format $precision $ssl $host $user $password $port  $database";
+    $interactive_command = $batch_command;
+    if $::opt_verbose and print STDERR "INFLUX COMMAND: [$interactive_command]\n";
+    return($batch_command,$interactive_command);
+}

 # Return the code for 'show processlist' in the chosen database dialect
 sub processlist {
@@ -823,6 +881,7 @@ sub processlist {
                       '    SYS.V_$SQL.SQL_ID = SYS.V_$SESSION.SQL_ID(+) '.
                       "AND username IS NOT NULL ".
                       "ORDER BY CPU_TIME DESC;"),
+      "influxdb" => "show queries;",
         );
     if($statement{$dbdriver}) {
         return $statement{$dbdriver};
@@ -842,6 +901,7 @@ sub tablelist {
          "oracle" => ("SELECT object_name FROM user_objects WHERE object_type = 'TABLE';"),
          "sqlite" => ".tables",
          "sqlite3" => ".tables",
+     "influxdb" => "show measurements;",
         );
     if($statement{$dbdriver}) {
         return $statement{$dbdriver};
@@ -861,6 +921,7 @@ sub dblist {
                           "WHERE datname NOT IN ('template0','template1','postgres') ".
                           "ORDER BY datname ASC;"),
          "oracle" => ("select * from user_tablespaces;"),
+     "influxdb" => "show databases;",
         );
     if($statement{$dbdriver}) {
         return $statement{$dbdriver};
@@ -1041,7 +1102,7 @@ sub parse_dburl {
     # sql:mysql://[[user][:password]@][host][:port]/[database[?sql query]]

     if($url=~m!(?:sql:)? # You can prefix with 'sql:'
-               ((?:oracle|ora|mysql|pg|postgres|postgresql)(?:s|ssl|)|
+               ((?:oracle|ora|mysql|pg|postgres|postgresql|influx|influxdb)(?:s|ssl|)|
                  (?:sqlite|sqlite2|sqlite3)):// # Databasedriver ($1)
                (?:
                 ([^:@/][^:@]*|) # Username ($2)


Jesse Becker <hawson>

 

(Note: upload size limit is set to 16384 kB, after insertion of the required escape characters.)

Attach Files:
   
   
Comment:
   

No files currently attached

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -email is unavailable- added by tange (Posted a comment)
  • -email is unavailable- added by hawson (Submitted the item)
  •  

    There are 0 votes so far. Votes easily highlight which items people would like to see resolved in priority, independently of the priority of the item set by tracker managers.

    Only logged-in users can vote.

     

    Follow 5 latest changes.

    Date Changed by Updated Field Previous Value => Replaced by
    2022-09-05 tange StatusReady For Test Fixed
        Open/ClosedOpen Closed
    2022-07-07 tange StatusIn Progress Ready For Test
    2022-07-07 tange StatusNone In Progress
        Assigned toNone tange

    Back to the top

    Powered by Savane 3.13-02a9.
    Corresponding source code