How to run sql from the command line against a dremio instance

Dremio University
1

I am writing software in collaboration with Claude 3.7 using Cline in VS Code. I like to give my assistant access to my local Dremio instance, which works best using the command line. I found the dremio_client project, but that project is archived.
Its cli looked like this:

dremio_client query --sql 'select * from sys.options'

Which would be very helpful to me.

Are there any similar tools I could use; preferably sth that ships with dremio_oss?

2

@serra You can use something like isql using Linux ODBC and run SQL from command line

isql -v ArrowFlightSQLODBCDSN
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
1 Like
3

This works great on my Linux machine. Sadly, the ODBC driver is not compiled for Mac M1. To run it on my Mac i could run unixodbc/isql through Rosetta using this workaround but I’m not sure how this affects my python dev and odbc setup.

On my mac I use a Python script based on @AlexMerced 's post connecting-to-dremio-using-apache-arrow-flight-in-python; that works good-enough for my coding assistant.

4

Hi @serra Thanks for the feedback, are you saying you have tested the below and it does not work on M chip?

To use isql with ODBC on macOS, you’ll need to install both unixODBC and the appropriate ODBC driver for your database. After installation, you can configure a Data Source Name (DSN) and then use isql to test the connection.

  1. Install unixODBC:
  • Open Terminal.
  • If you don’t have it, install Homebrew using the command: /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)".
  • Install unixODBC with: brew install unixodbc.
  • If you have an older version of macOS (like 10.5 and earlier), you might need to install iODBC from this link and use its configuration files.
  1. Install the ODBC Driver:
  • Download the ODBC driver for your database (e.g., Microsoft SQL Server, MySQL, etc.).
  • Follow the driver’s specific installation instructions.
  • For Microsoft SQL Server, you can use Homebrew:
    • brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
    • brew update
    • brew install msodbcsql18 mssql-tools18
  1. Configure a Data Source Name (DSN):
  • Using odbc.ini: You can configure DSNs in the ~/.odbc.ini file (for user-specific DSNs) or /Library/ODBC/odbc.ini (for system-wide DSNs).
  • Example odbc.ini entry:

Code

     [MySQL]     Driver = MySQL ODBC Driver     Server = localhost     Port = 3306     Database = mydatabase     User = myuser     Password = mypassword
  • Using ODBC Administrator: You can also use the ODBC Administrator (in the Utilities folder of the Applications folder) to configure DSNs graphically.
  1. Test the Connection with isql:
  • Open Terminal.
  • Use the isql command to connect to your DSN: isql -v [your_dsn_name]. Replace [your_dsn_name] with the DSN you configured.
  • For example: isql -v MySQL.
  • You should see a prompt indicating a successful connection
5

Indeed, there is not a suitable Dremio ODBC driver for M1 chip.

I can use isql for other database for which a suitable driver is available (works with MySQL, for instance).

6

@serra Dremio should have a native Apple Silicon ODBC driver for M1 chip via flight. Are you connecting your visualization tool using Flight ODBC?