Configuring Drill to query an RDBMS

As indicated in the previous section, to connect Drill to a MySQL database, you first need to copy the JDBC driver to the jars/3rdparty folder, or for a production Drill, your site directory (see Chapter 9).

Next, start Drill and navigate to the Storage tab in the user interface. At the bottom of the page, go to the section labeled New Storage Plugin. Type a name for the storage plug-in, and then click the Create button. This name is how the storage plug-in will be accessed in queries. It should go without saying that the RDBMS must be up and running to successfully query it from Drill.

In the configuration panel, enter one of the configurations listed in the following subsections.

{
  "type": "jdbc",
  "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
  "url": "jdbc:sqlserver://host:1433;databaseName=database_name",
  "username": "username",
  "password": "password",
  enabled: "true"

}

{
  "type": "jdbc",
  "driver": "com.mysql.jdbc.Driver",
  "url": "jdbc:mysql://localhost:3306",
  "username": "username",
  "password": "password",
  "enabled": true
} 

{
  "type": "jdbc",
  "driver": "oracle.jdbc.OracleDriver",
  "url": "jdbc:oracle:thin:username/password@host:1521/database"
  "enabled": true
}

{
  "type": "jdbc",
  "driver": "org.postgresql.Driver",
  "url": "jdbc:postgresql://host/database",
  "username": "username",
  "password": "password",
  "enabled": true
}

{
  "type": "jdbc",
  "driver": "org.sqlite.JDBC",
  "url": "jdbc:sqlite:path_to_database_file", 
  "enabled": true
}

Querying an RDBMS from Drill

After you have provided the configuration string, click Enable; if all went well, you should get a success message. You should now be able to use the database in a Drill query. You should structure the FROM clause as shown here:

FROM storage_plugin.database.table

Thus, a completed query would look like this:

SELECT *
FROM sqlite.`genres`;

This query returns all of the results from a table called genres in the chinook SQLite database.

Other uses of the drill JDBC storage plug-in

In addition to being able to query traditional databases that support JDBC, you can also access other data sources that have JDBC drivers. One example of this is data.world, an online platform that hosts open datasets for public use. As it turns out, a JDBC driver is available at the data.world GitHub repository.

After you have downloaded and installed the data.world JDBC driver, you can query data stored there as if it were hosted locally. To configure Drill to do this, open Drill’s web interface and, at the top of the screen, click the Storage tab. In the New Storage Plugin section, click Create. In the blank window that opens, enter the following configuration:

{
 "type": "jdbc",
 "driver": "world.data.jdbc.Driver",
 "url": "jdbc:data:world:sql:username:dataset",
 "username": "username",
 "password": "API Key",
 "enabled": true
}

You will need to get an API key from data.world, which is available after you have an account. Save this plug-in as dw. After you’ve set up the storage plug-in, all you really need to do to query a data.world dataset is modify the FROM clause of your query.

The data.world driver is a little different than a regular Drill FROM clause in that it has four parts, whereas a traditional Drill data source has only three:

FROM dw.username.dataset.filename

We uploaded a spreadsheet to data.world that contained a listing of MAC addresses and the manufacturers of the associated devices. To query this data, you would enter a query like the following:

SELECT *
FROM dw.cgivre.`mac-address-manufacturers`.
`20170426mac_address.csv/20170426mac_address`

Querying data from HBase

Querying data in HBase is significantly more complicated than other data sources because you will need to understand how data is stored in HBase. HBase stores data in bytes or byte arrays, and to be able to performantly query this data you need to understand the underlying data structure. From Drill, you can access individual columns can by using the following pattern:

table_name.column_family_name.column_name

HBase stores all columns as byte arrays stored in Drill as a VARBINARY column. To work with an HBase column, you first convert the VARBINARY column into another data type using either the CAST() function for numeric fields or CONVERT_FROM() for all other fields. The following example queries a fictitious table in HBase and converts the results into readable results:

SELECT 
    CONVERT_FROM(tbl.sales.customerid, 'UTF8') AS customerID,
    CAST(tbl.sales.order_amount AS FLOAT) AS order_amount
FROM hbase.table

In production systems, HBase will use timestamps or dates as row keys that are encoded as big-endian. By default, however, Drill assumes that the data is unsorted little-endian. When you use the CONVERT_TO() and CONVERT_FROM() functions on big-endian data, Drill will optimize the scanning process. Drill also provides several data types for big-endian conversions that all end in _BE, which are intended to be used with HBase. You can find a complete list in the Drill documentation.

The BYTE_SUBSTR() function is also very useful when querying HBase; it separates the components of a composite HBase row key. The following example shows a performant query in HBase:

SELECT 
    CONVERT_FROM(BYTE_SUBSTR(row_key, 1, 8), 'BIGINT_BE') AS row_key
FROM hbase.table

When the data is encoded in sorted byte arrays Drill can take advantage of this to improve performance, but it requires you to convert your data into an _OB data type. The following example demonstrates how to improve the performance of an HBase query by limiting the data scanned to only those rows with keys between 0 and 100:

SELECT 
    CONVERT_FROM(row_key, 'INT_OB') AS row_key
FROM hbase.table
WHERE CONVERT_FROM(row_key, 'INT_OB') >= CAST(0 AS INT) 
    AND CONVERT_FROM(row_key ,'INT_OB') < 100

Connecting Drill to Hive

To connect Drill to Hive, you must first determine whether the Hive cluster is using the embedded or a remote metastore. Normally, in a production environment, Hive uses a remote metastore; if it is not, you might want to talk with your systems administrator.

If you are using Hive with an embedded metastore, on the storage configuration page of the user interface, click the Update button adjacent to the Hive storage plug-in configuration. The default configuration for Hive is as follows:

  {
    "type": "hive",
    "configProps": {
      "hive.metastore.uris": "",
      "javax.jdo.option.ConnectionURL": 
          "jdbc:database://host:port/metastore database",
      "hive.metastore.warehouse.dir": "/tmp/drill_hive_wh",
      "fs.default.name": "file:///",
      "hive.metastore.sasl.enabled": "false"
    },
    "enabled": true,
  }

hive --service metastore

For Drill to connect to Hive with an embedded metastore, you must first set the fs.default.name to the default location of the files that you want to query. This can be either the local filesystem (file:///), an HDFS root path on the default HDFS namenode (hdfs://), or the path to a specific HDFS namenode (hdfs://host:port).

The configuration option javax.jdo.option.ConnectionURL requires a JDBC connection string to the metastore database. As with an RDBMS, you need to copy the driver to the DRILL_PATH/jars/3rdparty folder for this to work.

After you have done all that, click Enable, and then you are ready to query Hive.

hive --service metastore

{
    "type": "hive",
    "configProps": {
       "hive.metastore.uris": "thrift://host:port",
       "hive.metastore.sasl.enabled": "false",
       "fs.default.name": "hdfs://hdfs_host/",
    },
    "enabled": true
  }

"hbase.zookeeper.quorum": "zkhost1,zkhost2",
"hbase.zookeeper.property.clientPort":"2181"

store.hive.parquet.optimize_scan_with_native_reader

Querying streaming data

After you have registered the Kafka storage plug-in, you can execute queries against data in Kafka as if it were any other data source. It is important to remember that Kafka contains streaming data, and when you query data from Drill, it will scan all the Kafka messages from the earliest (most recent) offset to the latest.

Because data is constantly flowing into Kafka, every time you query (or consume) data, Drill will reset the offset pointer. You can change this option in the configuration for the Kafka storage plug-in.

Improving the performance of Kafka queries

In Drill 1.14, there is an enhancement to the Kafka plug-in that significantly reduces query time. Prior to this version, Drill would scan all the data in a topic before filtering; however, as of version 1.14, Drill can filter on the Kafka metadata, which will greatly reduce the volume of data that Drill is scanning. At present, there are three fields that you can include in the WHERE clause of your queries that will enhance performance:

kafkaPartitionId

Can be used with =, >, >=, <, <=.

kafkaMsgOffset

Can be used with =, >, <=, <, <=.

kafkaMsgTimestamp

Maps to the timestamp for each Kafka message. Drill can use this field only when you use the operators =, >, >=.

We recommend that you include these fields in predicates in the WHERE clause of your queries in your queries. For example:

WHERE kafkaMsgTimestamp > 'some date'

Querying data on Amazon S3

To query data on Amazon S3, you also need an access key and secret key.

Getting access credentials for S3

To obtain access credentials, first log in to Amazon S3, and then, in the upper-right corner, click your name to open your personal settings menu, as shown in Figure 6-2. Click the My Security Credentials option.

Figure 6-2. AWS account menu

Next, click the “Access keys” link and generate the access and secret keys (see Figure 6-3).

Warning

Be sure to save the keys in a safe place; you cannot retrieve them again after you have generated them.

Figure 6-3. Generating security credentials

After you have obtained your access and secret keys, the next step is to configure Drill with your AWS credentials. To do this, add the following code to the file $DRILL_HOME/conf/core-site.xml, replacing the placeholder values with your credentials:

<configuration>
  <property>
    <name>fs.s3a.access.key</name>
    <value>YOUR_ACCESSKEY</value>
  </property>
  <property>
    <name>fs.s3a.secret.key</name>
    <value>YOUR_SECRETKEY</value>
  </property>
  <property>
     <name>fs.s3a.connection.maximum</name>
     <value>100</value>
  </property>
  <property>
     <name>fs.s3a.endpoint</name>
     <value>s3.REGION.amazonaws.com</value>
  </property>
</configuration>

When you have added these options to your configuration file, the next step is to install the driver and configure the storage plug-in.

Fundamentally, the configuration to query Amazon S3 is the same as querying the local filesystem (dfs), with the only difference being the configuration string having an s3a address instead of file:///:

"connection":"s3a://your_S3_bucket"

Just as with the dfs storage plug-in, you can set up workspaces and file types; thus, a complete s3a configuration might look like this:

{ 
   "type": "file", 
   "enabled": true, 
   "connection": "s3a://your s3a bucket", 
   "workspaces": { 
       "root": { 
          "location": "/", 
          "writable": false, 
          "defaultInputFormat": null 
    }, 
    "formats": { 
        "csv": { 
            "type": "text", 
            "extensions": [ "csv" ], 
            "delimiter": "," 
        }
    } 
}

Querying Minio datastores from drill

Minio is a high-performance distributed object storage server that you can easily set up to query with Drill. After you have Minio up and running from an accessible IP address, you need to modify the core-site.xml file as shown here:

 <configuration>
     <property>
         <name>fs.s3a.access.key</name>
         <value>YOUR_ACCESS_KEY</value>
     </property>
     <property>
        <name>fs.s3a.secret.key</name>
        <value>YOUR_SECRET_KEY</value>
     </property>
     <property>
       <name>fs.s3a.endpoint</name>
       <value>http://minio_host:9000</value>
     </property>
     <property>
       <name>fs.s3a.connection.ssl.enabled</name>
       <value>false </value>
     </property>
     <property>
       <name>fs.s3a.path.style.access</name>
       <value>true</value>
     </property>
 </configuration>

When this is complete, follow the instructions for connecting to an S3 bucket and you will be able to query your data stored using Minio!

Connecting to other cloud storage services

In addition to being able to directly query data in an Amazon S3 bucket, Drill can query data in GCS or Microsoft Azure. The basic procedure is similar: download and install the driver, set up the credentials in the core-site.xml file, and then create a storage plug-in similar to the dfs plug-in, with the connection string for the cloud storage provider.

The following steps download and unzip the Azure driver to the Drill jars/3rdparty folder (don’t forget to restart your Drillbits after adding drivers):

$ cd $DRILL_HOME/jars/3rdparty/
$ sudo wget http://central.maven.org/maven2/org/apache/hadoop/hadoop-azure/
2.7.1/hadoop-azure-2.7.1.jar
$ sudo wget http://central.maven.org/maven2/com/microsoft/azure/azure-storage/
2.0.0/azure-storage-2.0.0.jar

Next, add the following property to the core-site.xml file:

<property>
    <name>fs.azure.account.key.your_data_files.blob.core.windows.net</name>
    <value><your_azure_account_key></value>
</property>

Finally, here’s the connection string for Azure:

wasb://your_container@your_datafiles.blob.core.windows.net

To connect Drill to GCS, download the drivers from the GCS website and copy them to the $DRILL_HOME/jars/3rdparty folder.

As with Azure, duplicate the settings for dfs and change the connection string to the one shown in the following snippet, replacing the placeholder with your bucket name:

"connection": "gs://your_bucket_name"

Special considerations for time series data

When you query time series data with Drill, you must specify certain configuration parameters in the FROM clause in your query. Table 6-1 lists the three mandatory parameters.

Additionally, there are two optional parameters, explained in Table 6-2.

The FROM clause of a Drill query must therefore look like the following example:

SELECT fields
FROM openTSDB.`(metric=metric_name, 
   start=start_time, 
   aggregator=aggregation_function)`

As a final example, the query that follows returns the aggregated and grouped values from the clicks.speed.test table, limited to the last year:

SELECT `timestamp`, SUM(`aggregated value`) AS avg_sum
FROM openTSDB.`(metric=clicks.speed.test, 
        aggregator=avg, 
        start=1y-ago)`
GROUP BY `timestamp`