Apache Drill

This example provides how to use Apache Drill external with NASA HDF-EOS5/HDF5 data products. Unfortunately, Apache Drill doesn't support HDF-EOS2/HDF4 yet.

Download

The latest Apache Drill is version 1.19.

apache-drill-1.19.0.tar.gz

Installation

Please read installation guide. The following sample steps are for Linux/Mac users.

$tar -zxvf apache-drill-1.19.0.tar.gz
$cd apache-drill-1.19.0
$./bin/drill-embedded

Once the above command runs successfully, you will get the Apache Drill prompt.

    Apache Drill 1.19.0

    "A little SQL for your NoSQL."

    apache drill>

To exit from Apache Drill prompt, use !quit.

Configure HDF5 Plug-in

Add .he5 and .nc4 extensions

By default, HDF5 plug-in works only with .h5 extension. Many NASA HDF-EOS products have extensions of .he5 and .nc4 as well.

To allow more extensions for HDF5 Plug-in, go to http://your_machine:8047/storage. Click Storage and press Update button for dfs.

Look for hdf5 under formats section of JSON. Edit extensions list values to include "he5" and "nc4" as shown in the Figure 1 below.

Figure 1. Configure Extension

Allow S3 access

If you run Apache Drill on an AWS EC2 instance, it is possible to query HDF5 on S3 using role-based access control because Drill does not support AWS temporary credentials [1].

To enable S3 access, create the following core-site.xml file under apache-drill-1.19.0/conf/ directory.

<configuration> <property> <name>fs.s3a.aws.credentials.provider</name> <value>com.amazonaws.auth.InstanceProfileCredentialsProvider</value> </property> </configuration>

After adding the above configuration file, (re)start Apache Drill. Then, enable S3 Plug-in using the Storage menu in web interface. Update connection: parameter to specify your bucket (Figure 2). Then, press Update button.

Figure 2. Configure S3 Bucket

Query an HDF-EOS5 FILE

Download and copy a sample netCDF-4/HDF5 GPM file into Apache Drill dfs store (e.g., /tmp/ directory).

$cp 3B-DAY-L.MS.MRG.3IMERG.20190331-S000000-E235959.V06.nc4.nc4 /tmp/

Query the copied file from Apache Drill command line interpreter.

  
      apache drill> SELECT path, data_type, file_name FROM dfs.`/tmp/3B-DAY-L.MS.MRG.3IMERG.20190331-S000000-E235959.V06.nc4.nc4`;

Querying Collection of HDF-EOS5 Files

One nice feature about Apache Drill is that you can query multiple files. Create a directory called /tmp/hdf5 and put all 8 files from here after downloading them.

      $mkdir /tmp/hdf5

      $cp *.nc4 /tmp/hdf5/

      apache drill> SELECT path, data_type, file_name FROM dfs.`/tmp/hdf5`;

Querying HDF-EOS5 File on S3

Once core-site.xml is properly configured and S3 Plug-in is enabled with a right bucket name, replace dts with s3 in your SQL as follows.

apache dril>SELECT path, data_type, file_name FROM s3.`dset.h5`;

To read a specific dataset, use table() and defaultPath.

     apache drill>SELECT * FROM table(s3.`dset.h5` (type => 'hdf5', defaultPath => '/dset'));

    

+-----------+-----------+-----------+-----------+-----------+-----------+ | int_col_0 | int_col_1 | int_col_2 | int_col_3 | int_col_4 | int_col_5 | +-----------+-----------+-----------+-----------+-----------+-----------+ | 1 | 2 | 3 | 4 | 5 | 6 | | 7 | 8 | 9 | 10 | 11 | 12 | | 13 | 14 | 15 | 16 | 17 | 18 | | 19 | 20 | 21 | 22 | 23 | 24 | +-----------+-----------+-----------+-----------+-----------+-----------+

Read Attributes

Use SELECT attributes to read attributes from an HDF-EOS5 file. The following example uses a sample HDF5 SMAP file.

      apache drill> SELECT attributes FROM dfs.`/tmp/SMAP_L4_SM_gph_20200915T193000_Vv5014_001.h5`;

+----------------------------------------------------------------------------------+ | attributes | +----------------------------------------------------------------------------------+ | {"standard_name":"projection_y_coordinate","units":"m","CLASS":"DIMENSION_SCALE","axis":"Y","long_name":"Y coordinate of cell center in EASE 2.0 global projection","NAME":"y"} | | {} | | {"units":"Pa","long_name":"Surface pressure","grid_mapping":"EASE2_global_projection","coordinates":"/cell_lat /cell_lon"} | | {"units":"W m-2","long_name":"Downward ground heat flux into layer 1 of soil heat diffusion model","grid_mapping":"EASE2_global_projection","coordinates":"/cell_lat /cell_lon"} | ...

Read a Dataset

Use WHERE path to read a specific dataset from an HDF-EOS5 file.

  
      apache drill> SELECT * FROM dfs.`/tmp/SMAP_L4_SM_gph_20200915T193000_Vv5014_001.h5` WHERE path='/cell_lon';

+-----------+-----------+----------------------------------------------+-----------+---------+---------------+-------------------+--------------+----------------------------------------------------------------------------------+----+----------------------------------------------------------------------------------+------+-----------+----+ | path | data_type | file_name | data_size | is_link | element_count | dataset_data_type | dimensions | attributes | y | float_data | time | long_data | x | +-----------+-----------+----------------------------------------------+-----------+---------+---------------+-------------------+--------------+----------------------------------------------------------------------------------+----+----------------------------------------------------------------------------------+------+-----------+----+ | /cell_lon | DATASET | SMAP_L4_SM_gph_20200915T193000_Vv5014_001.h5 | 25048576 | false | 6262144 | float | [1624, 3856] | {"units":"degrees","long_name":"The longitude of the center of each cell in the cylindrical 9 km Earth-fixed EASE-Grid 2.0. Zero longitude represents the Prime Meridian. Positive longitudes represent locations to the East of the Prime Meridian. Negative longitudes represent locations to the West of the Prime Meridian.","grid_mapping":"EASE2_global_projection"} | [] | [[-179.95332,-179.85997,-179.7666,-179.67325,-179.57988,-179.48653,-179.39316,-179.2998,-179.20644,-179.11308,-179.01971,-178.92636,-178.833,-178.73964,-178.64627,-178.55292,-178.45955,-178.3662,-178.27283,-178.17946,-178.0861,-177.99274,-177.89938,-177.80602,-177.71266,-177.6193,-177.52594,-177.43257,-177.33922,-177.24585,-177.1525,-177.05913,-176.96577,-176.8724,-176.77905,-176.68568,-176.59233,-176.49896,-176.40561,-176.31224,-176.21889,-176.12552,-176.03217,-175.9388,-175.84544,-175.75208,-175.65872,-175.56535,-175.472,-175.37863,-175.28528,-175.19191,-175.09856,-175.00519,-174.91183,-174.81847,-174.72511,-174.63174,-174.53839,-174.44502,-174.35167,-174.2583,-174.16495,-174.07158,-173.97823,-173.88486,-173.7915,-173.69814,-173.60478,-173.51141,-173.41806,-173.32469,-173.23134,-173.13797,-173.04462,-172.95125,-172.8579,-172.76453,-172.67117,-172.5778,-172.48445,-172.39108,-172.29773,-172.20436,-172.11101,-172.01764,-171.92429,-171.83092,-171.73756,-171.6442,-171.55084,-171.45747,-171.36412,-171.27075,-171.1774,-171.08403,-170.99066,-170.89731,-170.80394,-170.71059],[-179.95332,-179.85997,-179.7666,-179.67325,-179.57988,-179.48653,-179.39316,-179.2998,-179.20644,-179.11308,-179.01971,-178.92636,-178.833,-178.73964,-178.64627,-178.55292,-178.45955,-178.3662,-178.27283,-178.17946,-178.0861,-177.99274,-177.89938,-177.80602,-177.71266,-177.6193,-177.52594,-177.43257,-177.33922,-177.24585,-177.1525,-177.05913,-176.96577,-176.8724,-176.77905,-176.68568,-176.59233,-176.49896,-176.40561,-176.31224,-176.21889,-176.12552,-176.03217,-175.9388,-175.84544,-175.75208,-175.65872,-175.56535,-175.472,-175.37863,-175.28528,-175.19191,-175.09856,-175.00519,-174.91183,-174.81847,-174.72511,-174.63174,-174.53839,-174.44502,-174.35167,-174.2583,-174.16495,-174.07158,-173.97823,-173.88486,-173.7915,-173.69814,-173.60478,-173.51141,-173.41806,-173.32469,-173.23134,-173.13797,-173.04462,-172.95125,-172.8579,-172.76453,-172.67117,-172.5778,-172.48445,-172.39108,-172.29773,-172.20436,-172.11101,-172.01764,-171.92429,-171.83092,-171.73756,-171.6442,-171.55084,-171.45747,-171.36412,-171.27075,-171.1774,-171.08403,-170.99066,-170.89731,-170.80394,-170.71059],[-179.95332,-179.85997,-179.7666,-179.67325,-179.57988,-179.48653,-179.39316,-179.2998,-179.20644,-179.11308,-179.01971,-178.92636,-178.833,-178.73964,-178.64627,-178.55292,-178.45955,-178.3662,-178.27283,-178.17946,-178.0861,-177.99274,-177.89938,-177.80602,-177.71266,-177.6193,-177.52594,-177.43257,-177.33922,-177.24585,-177.1525,-177.05913,-176.96577,-176.8724,-176.77905,-176.68568,-176.59233,-176.49896,-176.40561,-176.31224,-176.21889,-176.12552,-176.03217,-175.9388,-175.84544,-175.75208,-175.65872,-175.56535,-175.472,-175.37863,-175.28528,-175.19191,-175.09856,-175.00519,-174.91183,-174.81847,-174.72511,-174.63174,-174.53839,-174.44502,-174.35167,-174.2583,-174.16495,-174.07158,-173.97823,-173.88486,-173.7915,-173.69814,-173.60478,-173.51141,-173.41806,-173.32469,-173.23134,-173.13797,-173.04462,-172.95125,-172.8579,-172.76453,-172.67117,-172.5778,-172.48445,-172.39108,-172.29773,-172.20436,-172.11101,-172.01764,-171.92429,-171.83092,-171.73756,-171.6442,-171.55084,-171.45747,-171.36412,-171.27075,-171.1774,-171.08403,-170.99066,-170.89731,-170.80394,-170.71059],[-179.95332,-179.85997,-179.7666,-179.67325,-179.57988,-179.48653,-179.39316,-179.2998,-179.20644,-179.11308,-179.01971,-178.92636,-178.833,-178.73964,-178.64627,-178.55292,-178.45955,-178.3662,-178.27283,-178.17946,-178.0861,-177.99274,-177.89938,-177.80602,-177.71266,-177.6193,-177.52594,-177.43257,-177.33922,-177.24585,-177.1525,-177.05913,-176.96577,-176.8724,-176.77905,-176.68568,-176.59233,-176.49896,-176.40561,-176.31224,-176.21889,-176.12552,-176.03217,-175.9388,-175.84544,-175.75208,-175.65872,-175.56535,-175.472,-175.37863,-175.28528,-175.19191,-175.09856,-175.00519,-174.91183,-174.81847,-174.72511,-174.63174,-174.53839,-174.44502,-174.35167,-174.2583,-174.16495,-174.07158,-173.97823,-173.88486,-173.7915,-173.69814,-173.60478,-173.51141,-173.41806,-173.32469,-173.23134,-173.13797,-173.04462,-172.95125,-172.8579,-172.76453,-172.67117,-172.5778,-172.48445,-172.39108,-172.29773,-172.20436,-172.11101,-172.01764,-171.92429,-171.83092,-171.73756,-171.6442,-171.55084,-171.45747,-171.36412,-171.27075,-171.1774,-171.08403,-170.99066,-170.89731,-170.80394,-170.71059],[-179.95332,-179.85997,-179.7666,-179.67325,-179.57988,-179.48653,-179.39316,-179.2998,-179.20644,-179.11308,-179.01971,-178.92636,-178.833,-178.73964,-178.64627,-178.55292,-178.45955,-178.3662,-178.27283,-178.17946,-178.0861,-177.99274,-177.89938,-177.80602,-177.71266,-177.6193,-177.52594,-177.43257,-177.33922,-177.24585,-177.1525,-177.05913,-176.96577,-176.8724,-176.77905,-176.68568,-176.59233,-176.49896,-176.40561,-176.31224,-176.21889,-176.12552,-176.03217,-175.9388,-175.84544,-175.75208,-175.65872,-175.56535,-175.472,-175.37863,-175.28528,-175.19191,-175.09856,-175.00519,-174.91183,-174.81847,-174.72511,-174.63174,-174.53839,-174.44502,-174.35167,-174.2583,-174.16495,-174.07158,-173.97823,-173.88486,-173.7915,-173.69814,-173.60478,-173.51141,-173.41806,-173.32469,-173.23134,-173.13797,-173.04462,-172.95125,-172.8579,-172.76453,-172.67117,-172.5778,-172.48445,-172.39108,-172.29773,-172.20436,-172.11101,-172.01764,-171.92429,-171.83092,-171.73756,-171.6442,-171.55084,-171.45747,-171.36412,-171.27075,-171.1774,-171.08403,-170.99066,-170.89731,-170.80394,-170.71059],[-179.95332,-179.85997,-179.7666,-179.67325,-179.57988,-179.48653,-179.39316,-179.2998,-179.20644,-179.11308,-179.01971,-178.92636,-178.833,-178.73964,-178.64627,-178.55292,-178.45955,-178.3662,-178.27283,-178.17946,-178.0861,-177.99274,-177.89938,-177.80602,-177.71266,-177.6193,-177.52594,-177.43257,-177.33922,-177.24585,-177.1525,-177.05913,-176.96577,-176.8724,-176.77905,-176.68568,-176.59233,-176.49896,-176.40561,-176.31224,-176.21889,-176.12552,-176.03217,-175.9388,-175.84544,-175.75208,-175.65872,-175.56535,-175.472,-175.37863,-175.28528,-175.19191,-175.09856,-175.00519,-174.91183,-174.81847,-174.72511,-174.63174,-174.53839,-174.44502,-174.35167,-174.2583,-174.16495,-174.07158,-173.97823,-173.88486,-173.7915,-173.69814,-173.60478,-173.51141,-173.41806,-173.32469,-173.23134,-173.13797,-173.04462,-172.95125,-172.8579,-172.76453,-172.67117,-172.5778,-172.48445,-172.39108,-172.29773,-172.20436,-172.11101,-172.01764,-171.92429,-171.83092,-171.73756,-171.6442,-171.55084,-171.45747,-171.36412,-171.27075,-171.1774,-171.08403,-170.99066,-170.89731,-170.80394,-170.71059],[-179.95332,-179.85997,-179.7666,-179.67325,-179.57988,-179.48653,-179.39316,-179.2998,-179.20644,-179.11308,-179.01971,-178.92636,-178.833,-178.73964,-178.64627,-178.55292,-178.45955,-178.3662,-178.27283,-178.17946,-178.0861,-177.99274,-177.89938,-177.80602,-177.71266,-177.6193,-177.52594,-177.43257,-177.33922,-177.24585,-177.1525,-177.05913,-176.96577,-176.8724,-176.77905,-176.68568,-176.59233,-176.49896,-176.40561,-176.31224,-176.21889,-176.12552,-176.03217,-175.9388,-175.84544,-175.75208,-175.65872,-175.56535,-175.472,-175.37863,-175.28528,-175.19191,-175.09856,-175.00519,-174.91183,-174.81847,-174.72511,-174.63174,-174.53839,-174.44502,-174.35167,-174.2583,-174.16495,-174.07158,-173.97823,-173.88486,-173.7915,-173.69814,-173.60478,-173.51141,-173.41806,-173.32469,-173.23134,-173.13797,-173.04462,-172.95125,-172.8579,-172.76453,-172.67117,-172.5778,-172.48445,-172.39108,-172.29773,-172.20436,-172.11101,-172.01764,-171.92429,-171.83092,-171.73756,-171.6442,-171.55084,-171.45747,-171.36412,-171.27075,-171.1774,-171.08403,-170.99066,-170.89731,-170.80394,-170.71059],[-179.95332,-179.85997,-179.7666,-179.67325,-179.57988,-179.48653,-179.39316,-179.2998,-179.20644,-179.11308,-179.01971,-178.92636,-178.833,-178.73964,-178.64627,-178.55292,-178.45955,-178.3662,-178.27283,-178.17946,-178.0861,-177.99274,-177.89938,-177.80602,-177.71266,-177.6193,-177.52594,-177.43257,-177.33922,-177.24585,-177.1525,-177.05913,-176.96577,-176.8724,-176.77905,-176.68568,-176.59233,-176.49896,-176.40561,-176.31224,-176.21889,-176.12552,-176.03217,-175.9388,-175.84544,-175.75208,-175.65872,-175.56535,-175.472,-175.37863,-175.28528,-175.19191,-175.09856,-175.00519,-174.91183,-174.81847,-174.72511,-174.63174,-174.53839,-174.44502,-174.35167,-174.2583,-174.16495,-174.07158,-173.97823,-173.88486,-173.7915,-173.69814,-173.60478,-173.51141,-173.41806,-173.32469,-173.23134,-173.13797,-173.04462,-172.95125,-172.8579,-172.76453,-172.67117,-172.5778,-172.48445,-172.39108,-172.29773,-172.20436,-172.11101,-172.01764,-171.92429,-171.83092,-171.73756,-171.6442,-171.55084,-171.45747,-171.36412,-171.27075,-171.1774,-171.08403,-170.99066,-170.89731,-170.80394,-170.71059],[-179.95332,-179.85997,-179.7666,-179.67325,-179.57988,-179.48653,-179.39316,-179.2998,-179.20644,-179.11308,-179.01971,-178.92636,-178.833,-178.73964,-178.64627,-178.55292,-178.45955,-178.3662,-178.27283,-178.17946,-178.0861,-177.99274,-177.89938,-177.80602,-177.71266,-177.6193,-177.52594,-177.43257,-177.33922,-177.24585,-177.1525,-177.05913,-176.96577,-176.8724,-176.77905,-176.68568,-176.59233,-176.49896,-176.40561,-176.31224,-176.21889,-176.12552,-176.03217,-175.9388,-175.84544,-175.75208,-175.65872,-175.56535,-175.472,-175.37863,-175.28528,-175.19191,-175.09856,-175.00519,-174.91183,-174.81847,-174.72511,-174.63174,-174.53839,-174.44502,-174.35167,-174.2583,-174.16495,-174.07158,-173.97823,-173.88486,-173.7915,-173.69814,-173.60478,-173.51141,-173.41806,-173.32469,-173.23134,-173.13797,-173.044 | +-----------+-----------+----------------------------------------------+-----------+---------+---------------+-------------------+--------------+----------------------------------------------------------------------------------+----+----------------------------------------------------------------------------------+------+-----------+----+ 1 row selected (10.331 seconds)

Visualization with Apache Superset

Apache Superset is a web-based data visualization software that can connect to Apache Drill. Use sqlalchemy-drill [2] to visualize HDF5 data using Apache Drill.

Limitations

Apache Drill may crash on some HDF-EOS5 data products. Please report any issue on forum.

References

Last modified: 10/12/2021