Connect PolyBase to your Hive database Table: SQL Server 2016

A straight forward way to connect PolyBase to your Hadoop text-delimited Hive tables is to use the DELIMITEDTEXT format method (non-ORC files). The only difference is that with Hive tables, the file data has been internally migrated from a simple file format into a Hive table. But, while it is treated as a Hive table, it is still a file within Hadoop. And you can access that file using the PolyBase External Table declaration syntax. (For ORC tables see Connect PolyBase to your Hive ORC Table)

Before you can create an External Table in PolyBase, you first need both an External Data Source, and an appropriate External File format. And like any text-delimited External Tables in PolyBase, you'll need to define how the columns are delimited, the column names and data types, and the physical location of the file on the Hadoop server.

To get started, first navigate to the Hive user interface in Hadoop. For this example, we are using Hortonworks.  With the UI open, select/click on the Tables tab. This will show a listing of the Hive tables available.

HIVE table listing - notice the database name:  xademo.

 

With the Table listing open, locate the table. Our target table is call_detail_records. After locating the table, select/click on it. This brings up the Table Metadata page.

Metadata page shows the column names and datatypes stored in HIVE.

The Metadata page defines how Hive has named the column and their data type. We will need this to create our External Table. On the left, locate and select/click on the View File Location action. This takes you to the folder where the file is located, as well as providing the actual file name stored in the Hive database. 

File Browser - File Folder Location.

In our example, the file and folder name to be used for the External Table is:

  /apps/hive/warehouse/xademo.db/call_detail_records/cdrs.txt

In the file-folder name, notice the folder xademo.db. This represents the Hive database named xademo. The next folder, call_detail_records is identical to the Hive table name. The last value, cdrs.txt, is the file name used within Hadoop on the server. You will also notice that you are now in the File Browser, and not Hive.

Select/click the file (in our case cdrs.txt). This opens the file in viewing mode, so you can discover the file delimiter being used by Hive. In our case, it is a pipe.

Preview of the file data in Hadoop for the file cdrs.txt

For the Pipe delimiter, we'll need to create a new External File format, with this syntax:

CREATE EXTERNAL FILE FORMAT Pipefile

WITH (

       FORMAT_TYPE = DELIMITEDTEXT

              , FORMAT_OPTIONS (

              FIELD_TERMINATOR = '|'                  

                     ,USE_TYPE_DEFAULT =  TRUE ),

              DATA_COMPRESSION = 'org.apache.hadoop.io.compress.DefaultCodec'

       );

Using the column and data type values discovered earlier, we can now create the External Table using this syntax:

       CREATE EXTERNAL TABLE [dbo].HIVE_asfile_cdrs (

              PHONE         varchar(100) NULL ,

              TYPE_1        varchar(40) NULL ,

              IN_OUT        varchar(40) NULL ,

              REC_LOCAL     varchar(40) NULL ,

              DURATION      varchar(40) NULL ,

              REC_DATE      varchar(40) NULL ,

              TIME_         varchar(40) NULL ,

              ROAMING       varchar(40) NULL ,

              AMOUNT        varchar(40) NULL ,

              IN_NETWORK    varchar(40) NULL ,

              IS_PROMO      varchar(40) NULL ,

              TOLL_FREE     varchar(40) NULL ,

              BYTES         varchar(40) NULL ,

              TYPE_2        varchar(40) NULL

             )

           WITH (LOCATION='/apps/hive/warehouse/xademo.db/call_detail_records/cdrs.txt',

              DATA_SOURCE = hdp23 ,

              FILE_FORMAT = Pipefile           

              );

With the External Table created, test the connection by running a simple SELECT. If all goes well, your Hive table is now available via a PolyBase connection.