# Apache Drill dialect for SQLAlchemy.
---
The primary purpose of this is to have a working dialect for Apache Drill that can be used with Apache Superset.
https://superset.incubator.apache.org
Obviously, a working, robust dialect for Drill serves other purposes as well, but most of the iterative planning for this REPO will be based on working with Superset. Other changes will gladly be incorporated, as long as it doesn't hurt Superset integration.
## Installation
Installing the dialect is straightforward. Simply:
```
pip install sqlalchemy-drill
```
Alternatively, you can download the latest release from github and install from here:
```python
python3 -m pip install git+https://github.com/JohnOmernik/sqlalchemy-drill.git
```
## Usage with REST
Drill's REST API can execute queries with results streamed to JSON returned over chunked HTTP for Drill >= 1.19, otherwise with results buffered and then returned in a conventional HTTP response. A SQLAlchemy URL to connect to Drill over REST looks like the following.
```
drill+sadrill://<username>:<password>@<host>:<port>/<storage_plugin>?use_ssl=True
```
To connect to Drill running on a local machine running in embedded mode you can use the following connection string.
```
drill+sadrill://localhost:8047/dfs?use_ssl=False
```
### Supported URL query parameters
| Parameter | Type | Description |
| ------------------------- | ------- | -------------------------------------------------------------- |
| use_ssl | boolean | Whether to connect to Drill using HTTPS |
| verify_ssl | boolean | Whether to verify the server's TLS certificate |
| impersonation_target\[1\] | string | Username of a Drill user to be impersonated by this connection |
[1] Requires a build of Drill that incorporates the fix for DRILL-8168.
### Trailing metadata
Query result metadata returned by the Drill REST API is stored in the `result_md` field of the DB-API Cursor object. Note that any trailing metadata, i.e. metadata which comes after result row data, will only be populated after you have iterated through all of the returned rows. If you need this trailing metadata you can make the cursor object reachable after it has been completely iterated by obtaining a reference to it beforehand, as follows.
```python
r = engine.execute('select current_timestamp')
r.cursor.result_md # access metadata, but only leading metadata
cur = r.cursor # obtain a reference for use later
r.fetchall() # iterate through all result data
cur.result_md # access metadata, including trailing metadata
del cur # optionally delete the reference when done
```
### Drill < 1.19
In versions of Drill earlier than 1.19, all data values are serialised to JSON strings and column type metadata comes after the data itself. As a result, for these versions of Drill, the drill+sadrill dialect returns every data value as a string. To convert non-string data to its native type you need to typecast it yourself.
### Drill >= 1.19
In Drill 1.19 the REST API began making use of numeric types in JSON for numbers and times, the latter via a UNIX time representation while column type metadata was moved ahead of the result data. Because of this, the drill+sadrill dialect is able to return appropriate types for numbers and times when used with Drill >= 1.19.
## Usage with JDBC
Connecting to Drill via JDBC is a little more complicated than a local installation and complete instructions can be found on the Drill documentation here: https://drill.apache.org/docs/using-the-jdbc-driver/.
In order to configure SQLAlchemy to work with Drill via JDBC you must:
- Download the latest JDBC Driver available here: http://apache.osuosl.org/drill/
- Copy this driver to your classpath or other known path
- Set an environment variable called `DRILL_JDBC_DRIVER_PATH` to the full path of your driver location
- Set an environment variable called `DRILL_JDBC_JAR_NAME` to the name of the `.jar` file for the Drill driver.
Additionally, you will need to install `JayDeBeApi` as well as jPype version 0.6.3.
These modules are listed as optional dependencies and will not be installed by the default installer.
If the JDBC driver is not available, the dialect will throw errors when trying
to connect. In addition, sqlalchemy-drill will not launch a JVM for you so you
need to do this yourself with a call to JPype like the following. See the file
test-jdbc.py in this repo for a working example.
```python
jpype.startJVM("-ea", classpath="lib/*")
```
```
drill+jdbc://<username>:<passsword>@<host>:<port>
```
For a simple installation, this might look like:
```
drill+jdbc://admin:password@localhost:31010
```
## Usage with ODBC
In order to configure SQLAlchemy to work with Drill via ODBC you must:
- Install latest Drill ODBC Driver: https://drill.apache.org/docs/installing-the-driver-on-linux/
- Ensure that you have ODBC support in your system (`unixODBC` package for RedHat-based systems).
- Install `pyodbc` Python package.
This module is listed as an optional dependency and will not be installed by the default installer.
To connect to Drill with SQLAlchemy use the following connection string:
```
drill+odbc:///?<ODBC connection parameters>
```
Connection properties are available in the official documentation: https://drill.apache.org/docs/odbc-configuration-reference/
For a simple installation, this might look like:
```
drill+odbc:///?Driver=/opt/mapr/drill/lib/64/libdrillodbc_sb64.so&ConnectionType=Direct&HOST=localhost&PORT=31010&AuthenticationType=Plain&UID=admin&PWD=password
```
or for the case when you have DSN configured in `odbc.ini`:
```
drill+odbc:///?DSN=drill_dsn_name
```
**Note:** it's better to avoid using connection string with `hostname:port` or `username`/`password`, like 'drill+odbc://admin:password@localhost:31010/' but use only ODBC properties instead to avoid any misinterpretation between these parameters.
## Usage with Superset
For a complete tutorial on how to use Superset with Drill, read the tutorial on @cgivre's blog available here: http://thedataist.com/visualize-anything-with-superset-and-drill/.
## Current Status/Development Approach
Currently we can connect to drill, and issue queries for most visualizations and get results. We also enumerate table columns for some times of tables. Here are things that are working as some larger issues to work out. (Individual issues are tracked under issues)
- Connection to Drill via the databases tab in Superset succeeds
- You can do basic queries for most types of viz/tables
- There may be issues with advanced queries/joins. As you learn about new ones, please track in issues
### Many thanks
to drillpy and pydrill for code used in creating the original `drilldbapi.py` code for connecting!
### Docker
It is recommended to extend [the official Docker image](https://hub.docker.com/r/apache/superset) to include this Apache Drill driver:
```dockerfile
FROM apache/superset
# Switching to root to install the required packages
USER root
RUN pip install git+https://github.com/JohnOmernik/sqlalchemy-drill.git
# Switching back to using the `superset` user
USER superset
```
Raw data
{
"_id": null,
"home_page": "https://github.com/JohnOmernik/sqlalchemy-drill",
"name": "sqlalchemy-drill",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "SQLAlchemy Apache Drill",
"author": "John Omernik, Charles Givre, Davide Miceli, Massimo Martiradonna, James Turton",
"author_email": "john@omernik.com, cgivre@thedataist.com, davide.miceli.dap@gmail.com, massimo.martiradonna.dap@gmail.com, james@somecomputer.xyz",
"download_url": "https://files.pythonhosted.org/packages/ec/41/bb08ac8c123fc35f40a8791e68e9dec2d55ea2ab887700c3dd20e4d929f3/sqlalchemy_drill-1.1.4.tar.gz",
"platform": null,
"description": "# Apache Drill dialect for SQLAlchemy.\n\n---\n\nThe primary purpose of this is to have a working dialect for Apache Drill that can be used with Apache Superset.\n\nhttps://superset.incubator.apache.org\n\nObviously, a working, robust dialect for Drill serves other purposes as well, but most of the iterative planning for this REPO will be based on working with Superset. Other changes will gladly be incorporated, as long as it doesn't hurt Superset integration.\n\n## Installation\n\nInstalling the dialect is straightforward. Simply:\n\n```\npip install sqlalchemy-drill\n```\n\nAlternatively, you can download the latest release from github and install from here:\n\n```python\npython3 -m pip install git+https://github.com/JohnOmernik/sqlalchemy-drill.git\n```\n\n## Usage with REST\n\nDrill's REST API can execute queries with results streamed to JSON returned over chunked HTTP for Drill >= 1.19, otherwise with results buffered and then returned in a conventional HTTP response. A SQLAlchemy URL to connect to Drill over REST looks like the following.\n\n```\ndrill+sadrill://<username>:<password>@<host>:<port>/<storage_plugin>?use_ssl=True\n```\n\nTo connect to Drill running on a local machine running in embedded mode you can use the following connection string.\n\n```\ndrill+sadrill://localhost:8047/dfs?use_ssl=False\n```\n\n### Supported URL query parameters\n\n| Parameter | Type | Description |\n| ------------------------- | ------- | -------------------------------------------------------------- |\n| use_ssl | boolean | Whether to connect to Drill using HTTPS |\n| verify_ssl | boolean | Whether to verify the server's TLS certificate |\n| impersonation_target\\[1\\] | string | Username of a Drill user to be impersonated by this connection |\n\n[1] Requires a build of Drill that incorporates the fix for DRILL-8168.\n\n### Trailing metadata\n\nQuery result metadata returned by the Drill REST API is stored in the `result_md` field of the DB-API Cursor object. Note that any trailing metadata, i.e. metadata which comes after result row data, will only be populated after you have iterated through all of the returned rows. If you need this trailing metadata you can make the cursor object reachable after it has been completely iterated by obtaining a reference to it beforehand, as follows.\n\n```python\nr = engine.execute('select current_timestamp')\nr.cursor.result_md # access metadata, but only leading metadata\ncur = r.cursor # obtain a reference for use later\nr.fetchall() # iterate through all result data\ncur.result_md # access metadata, including trailing metadata\ndel cur # optionally delete the reference when done\n```\n\n### Drill < 1.19\n\nIn versions of Drill earlier than 1.19, all data values are serialised to JSON strings and column type metadata comes after the data itself. As a result, for these versions of Drill, the drill+sadrill dialect returns every data value as a string. To convert non-string data to its native type you need to typecast it yourself.\n\n### Drill >= 1.19\n\nIn Drill 1.19 the REST API began making use of numeric types in JSON for numbers and times, the latter via a UNIX time representation while column type metadata was moved ahead of the result data. Because of this, the drill+sadrill dialect is able to return appropriate types for numbers and times when used with Drill >= 1.19.\n\n## Usage with JDBC\n\nConnecting to Drill via JDBC is a little more complicated than a local installation and complete instructions can be found on the Drill documentation here: https://drill.apache.org/docs/using-the-jdbc-driver/.\n\nIn order to configure SQLAlchemy to work with Drill via JDBC you must:\n\n- Download the latest JDBC Driver available here: http://apache.osuosl.org/drill/\n- Copy this driver to your classpath or other known path\n- Set an environment variable called `DRILL_JDBC_DRIVER_PATH` to the full path of your driver location\n- Set an environment variable called `DRILL_JDBC_JAR_NAME` to the name of the `.jar` file for the Drill driver.\n\nAdditionally, you will need to install `JayDeBeApi` as well as jPype version 0.6.3.\nThese modules are listed as optional dependencies and will not be installed by the default installer.\n\nIf the JDBC driver is not available, the dialect will throw errors when trying\nto connect. In addition, sqlalchemy-drill will not launch a JVM for you so you\nneed to do this yourself with a call to JPype like the following. See the file\ntest-jdbc.py in this repo for a working example.\n\n```python\njpype.startJVM(\"-ea\", classpath=\"lib/*\")\n```\n\n```\ndrill+jdbc://<username>:<passsword>@<host>:<port>\n```\n\nFor a simple installation, this might look like:\n\n```\ndrill+jdbc://admin:password@localhost:31010\n```\n\n## Usage with ODBC\n\nIn order to configure SQLAlchemy to work with Drill via ODBC you must:\n\n- Install latest Drill ODBC Driver: https://drill.apache.org/docs/installing-the-driver-on-linux/\n- Ensure that you have ODBC support in your system (`unixODBC` package for RedHat-based systems).\n- Install `pyodbc` Python package.\n This module is listed as an optional dependency and will not be installed by the default installer.\n\nTo connect to Drill with SQLAlchemy use the following connection string:\n\n```\ndrill+odbc:///?<ODBC connection parameters>\n```\n\nConnection properties are available in the official documentation: https://drill.apache.org/docs/odbc-configuration-reference/\n\nFor a simple installation, this might look like:\n\n```\ndrill+odbc:///?Driver=/opt/mapr/drill/lib/64/libdrillodbc_sb64.so&ConnectionType=Direct&HOST=localhost&PORT=31010&AuthenticationType=Plain&UID=admin&PWD=password\n```\n\nor for the case when you have DSN configured in `odbc.ini`:\n\n```\ndrill+odbc:///?DSN=drill_dsn_name\n```\n\n**Note:** it's better to avoid using connection string with `hostname:port` or `username`/`password`, like 'drill+odbc://admin:password@localhost:31010/' but use only ODBC properties instead to avoid any misinterpretation between these parameters.\n\n## Usage with Superset\n\nFor a complete tutorial on how to use Superset with Drill, read the tutorial on @cgivre's blog available here: http://thedataist.com/visualize-anything-with-superset-and-drill/.\n\n## Current Status/Development Approach\n\nCurrently we can connect to drill, and issue queries for most visualizations and get results. We also enumerate table columns for some times of tables. Here are things that are working as some larger issues to work out. (Individual issues are tracked under issues)\n\n- Connection to Drill via the databases tab in Superset succeeds\n- You can do basic queries for most types of viz/tables\n- There may be issues with advanced queries/joins. As you learn about new ones, please track in issues\n\n### Many thanks\n\nto drillpy and pydrill for code used in creating the original `drilldbapi.py` code for connecting!\n\n### Docker\n\nIt is recommended to extend [the official Docker image](https://hub.docker.com/r/apache/superset) to include this Apache Drill driver:\n\n```dockerfile\nFROM apache/superset\n# Switching to root to install the required packages\nUSER root\nRUN pip install git+https://github.com/JohnOmernik/sqlalchemy-drill.git\n# Switching back to using the `superset` user\nUSER superset\n```\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Apache Drill for SQLAlchemy",
"version": "1.1.4",
"project_urls": {
"Download": "https://github.com/JohnOmernik/sqlalchemy-drill/archive/1.1.4.tar.gz",
"Homepage": "https://github.com/JohnOmernik/sqlalchemy-drill"
},
"split_keywords": [
"sqlalchemy",
"apache",
"drill"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "ec41bb08ac8c123fc35f40a8791e68e9dec2d55ea2ab887700c3dd20e4d929f3",
"md5": "32a059cac739ef7ec477ae03a897cc54",
"sha256": "05e385f8b89c4168ec8c59fae8c7df8242a2234a25d751e92ad1e31422209e51"
},
"downloads": -1,
"filename": "sqlalchemy_drill-1.1.4.tar.gz",
"has_sig": false,
"md5_digest": "32a059cac739ef7ec477ae03a897cc54",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 27702,
"upload_time": "2023-10-23T13:49:04",
"upload_time_iso_8601": "2023-10-23T13:49:04.001247Z",
"url": "https://files.pythonhosted.org/packages/ec/41/bb08ac8c123fc35f40a8791e68e9dec2d55ea2ab887700c3dd20e4d929f3/sqlalchemy_drill-1.1.4.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-10-23 13:49:04",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "JohnOmernik",
"github_project": "sqlalchemy-drill",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "sqlalchemy-drill"
}
JSON
