This article outlines the steps needed to setup ODBC access to Hive via Apache Knox from a Linux workstation. While there seems to be some "reasonable" documentation other there on setting up this access from Windows, it took quite some time to figure out how to do this from Linux. For this example, I am installing the Hortonworks Linux ODBC driver on CentOS Linux release 7.2.1511 then verifying it works using both the isql ODBC command line tool and PyODBC. Installation Steps 1) Install the UnixODBC Dependencies sudo yum install unixODBC 2) Download and Install Hortonworks ODBC Linux Driver I developed this guide using the below version of the driver. Note that the following URL is for the CentOS 7 version of the driver. If you are running an older version of the OS, then visit https://hortonworks.com/downloads/ and use the CentOS 6 driver. Also note that the below URL will likely change as new versions of the driver are released so it is a good idea to check the Hortonworks download page for the latest version when you run this procedure. I can tell you that a college used this procedure on RHEL 6.4 and used the CentOS 6 driver and it worked fine. wget http://public-repo-1.hortonworks.com/HDP/hive-odbc/2.1.5.1006/centos7/hive-odbc-native-2.1.5.1006-1.el7.x86_64.rpm sudo yum install hive-odbc-native-2.1.5.1006-1.el7.x86_64.rpm 3) Create $HOME/.odbc.ini You don't necessarily need this file. For example, you could build up a DSN string in code in a Python program that you use to connect to Hive using PyODBC. But, you can also just specify a DSN name that PyODBC then maps to an entry in your $HOME/.odbc.ini file. So, either way, here are the settings that I used to build up a minimal $HOME/.odbc.ini used in this guide. Simple copy the following values into $HOME/.odbc.ini. If you want to know the details of what each field means and its optional values, you can find it all at the end of the ODBC installation document. Note that the following sample entries have some fields that you MUST change that are environment and user specific. Namely the host, trustStorePassword, and ClientCert fields. All other fields should be left as they are. [ODBC Data Sources] # The below key 'Knox' can be anything but it MUST MATCH the key name used # in the section below. The right side is just descriptive and can be anything Knox=DSN for access to the production cluster using Beeline via Knox [Knox] # Optional description of this DSN Description=Hortonworks Knox QA DSN # Where the actual ODBC driver library is loaded from. This is the default # location where Hortonworks installs the driver Driver=/usr/lib/hive/lib/native/Linux-amd64-64/libhortonworkshiveodbc64.so # The hostname of the server running the Knox gateway Host=api01.quasar.local # The port used to connect to the Knox gateway port=8443 # The same httppath value used in a typical beeline connection string that # connects via Knox. For our cluster, it is always "quasar/jupstats/hive" HttpPath=quasar/jupstats/hive # The database you want to be on once beeline connects to Hive. This is basically # like executing the HQL "use my_database" command after the connection is made. schema=default # Must be 0 because we want to connect directly to Hiveserver2 inside the cluster. ServiceDiscoveryMode=0 # Must be 2 because we are using hiveserver2 inside the cluster. HiveServerType=2 # This indicates we need to provide a username and password in the connect string AuthMech=3 # This indicates the transport mode for Hive is HTTP, which is required for Knox ThriftTransport=2 # Indicates the connection to Knox uses SSL. Note that our Hive configuration has # hive.server2.use.SSL=false but, the connection to hiverserver2 is via Knox and, # to connect to Knox, you must use SSL. So, the ODBC driver is first connecting # to Knox hence we need SSL=1 here. If you were using ODBC to directly connect # to hiveserver2 (not via Knox), then, for the configuration we have in our cluster, # this value would need to be 0 SSL=1 # But NOT two-way SSL meaning no client side authentication - only server-side TwoWaySSL=0 # These two are the same values used in the beeline connect string. The password is # the password you used to add the Knox certificate to your local user key store. # The client certifificate path is just the path to that key store trustStorePassword=<your-trust-store-password> ClientCert=/home/mpetronic/keystore/keystore.jks # Must be 1 to support using self-signed certificates. Since we generated and # self-signed certificate in our cluster, we need this. AllowSelfSignedServerCert=1 # This prevents ODBC from doing query preprocessing which is not required since we are # running against a Hive-aware end point UseNativeQuery=1 Regarding the use of the UseNativeQuery=1 option, the ODBC docs say: This option specifies whether the driver uses native HiveQL queries, or converts them into an equivalent form in HiveQL. Enabled (1): The driver does not transform the queries emitted by an application, and executes HiveQL queries directly. Disabled (0): The driver transforms the queries emitted by an application and converts them into an equivalent form in HiveQL. Note: If the application is Hive-aware and already emits HiveQL, then enable this option to avoid the extra overhead of query transformation. Hive logs the queries that are run in various places like log files and in the Tez View. Admins need to be able to read these to help debug query execution problems. When UseNativeQuery=1 is set, the logged queries look like what you would normally expect - this: select count(*) from my_db.some_table where year=2016 and month=10 and day=2 But, if UseNativeQuery=0 is used, then the same query will end up looking like this: SELECT COUNT(1) AS `C_455850525f31` FROM `my_db`.`some_table` `C_6a7570315f73746174735f63726f5f6361706163697479` WHERE ((`C_6a7570315f73746174735f63726f5f6361706163697479`.`day` = 2) AND (`C_6a7570315f73746174735f63726f5f6361706163697479`.`year` = 2016) AND (`C_6a7570315f73746174735f63726f5f6361706163697479`.`month` = 10)) If you had to debug queries in logs, which would you prefer? Right! ALWAYS set UseNativeQuery=1. Plus, per the docs, this also improves performance by reducing the overhead of unnecessary query transformation. 4) Test your Connection via Command Line When you installed the unixODBC packages, it came with "isql", an interactive SQL CLI. Run the following command to see if you can connect to Hive via ODBC via Knox using the DSN you just created above. You should get some valid results from your query: [mpetronic@mpws Downloads]$ echo "show databases" | isql -v -d, Knox <your-Linux-username> <your-Linux-password> +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> show databases default test_db another_db 5) Test your Connection via PyODBC The following is a simple Python app that uses PyODBC to access the same database using the same DSN. Copy the below sample code to your workstation, read the comments in the script and do what they say to do to be able to run the program. Then run it to verify you can successfully connect and execute a query via Knox. It will print the query results to stdout. #!/usr/bin/env python import pyodbc import argparse ''' Demonstrates the bare bones code needed to access Hive via Beeline via Knox. For this sample to work, you must provide either the full ODBC configuration via a connection string or reference an odbc.ini file that already has a DSN defined for the connection to Knox you are trying to make. To go the odbc.ini route, do the following: 1. Create .odbc.ini in your $HOME directory. Note this is a hidden file and that is what the ODBC driver expects by default 2. Populate $HOME/.odbc.ini with the following values. These are the minimum values needed for the file to work with Knox. I am not going to explain all the values in detail. If you want to know what each one means, look them up in this doc: https://hortonworks.com/wp-content/uploads/2016/03/Hortonworks-Hive-ODBC-Driver-User-Guide.pdf. Also, change the host, trustStorePassword, and ClientCert values as needed for your setup. [ODBC Data Sources] # This key 'Knox' can be anything but it MUST MATCH the key name used in the # section below. The right side is just descriptive and can be anything Knox=DNS for access to Hive via Knox [Knox] # Optional description of this DSN Description=Hortonworks Knox QA DSN # Where the actually ODBC driver library is loaded from. This is the default # location where Hortonworks installs the driver Driver=/usr/lib/hive/lib/native/Linux-amd64-64/libhortonworkshiveodbc64.so # The hostname of the sever running the Knox gateway Host=api01.qa # The port used to connect to the Knox gateway port=8443 # The same Http path value used in a typical beeline connect string that # connects to Knox. For Quasar, it is always "quasar/jupstats/hive" HttpPath=quasar/jupstats/hive # The default database you want to be one once beeline connects to Hive schema=jup1_stats # Must be zero as we want to connect directly to Hiveserver2 inside the cluster. ServiceDiscoveryMode=0 # Must be 2 because we are using hiveserver2 which is what beeline uses as well HiveServerType=2 # This indicates we need to provide a username and passwork in the connect string AuthMech=3 # This indicates the transport mode for Hive is HTTP, which is required for Knox ThriftTransport=2 # Indicates the connection to Knox uses SSL SSL=1 # But NOT two-way SSL meaning no client side authentication - only server-side TwoWaySSL=0 # These two are the same values used in the beeline connect string. This is the # password you used when you added the Knox certificate to your own trust store trustStorePassword=<your-trust-store-password> ClientCert=/home/mpetronic/keystore/keystore.jks # Must be 1 to support the self-signed certificate mode we use for Knox AllowSelfSignedServerCert=1 3. Install dependencies for this script to run. First, install the unixODBC-devel package (via yum, if you can or download the RPM and install that way). This is needed for header files that are used build pyodbc. Then install PyODBC using "pip install pyodbc==3.0.10". Note that the pyodbc installation needs to build some source code so you will also need the gcc tools chain installed. 4. Run this script and provide the require command line options to test your connection To instead, NOT require the $HOME/.odbc.ini file, you can build a DSN string directly in code. Below is an example of how you can build a connection string to directly pass into the pyodbc.connect() method. Using this approach, you DO NOT need to have a .odbc.ini file present. This gives you full programmatic control of the process if you wish to use it this way. This simply builds a string like "A=1;B=2;C=3" containing all the exact same parameters that could be defined in the .odbc.ini file. ''' CONNECTION_STRING_EXAMPLE = ';'.join(''' Description=Hortonworks Knox DSN Driver=/usr/lib/hive/lib/native/Linux-amd64-64/libhortonworkshiveodbc64.so Host=api01 port=8443 HttpPath=quasar/jupstats/hive schema=default ServiceDiscoveryMode=0 HiveServerType=2 AuthMech=3 ThriftTransport=2 SSL=1 TwoWaySSL=0 trustStorePassword=<your-trust-store-password> ClientCert=/home/mpetronic/keystore/keystore.jks AllowSelfSignedServerCert=1 uid=<your-linux-username> pwd=<your-linux-password> '''.splitlines()) #=============================================================================== def parse_args(): parser = argparse.ArgumentParser() parser.add_argument('--user', '-u', type=str, required=True, help='User name used to connect to Hive with beeline via Knox.') parser.add_argument('--password', '-p', type=str, required=True, help='Password used to connect to Hive with beeline via Knox.') parser.add_argument('--dsn', '-d', type=str, required=True, help='The DSN name from $HOME/.odbc.ini to use for this connection.') return parser.parse_args() #=============================================================================== def execute_query(sql, dsn, user, password): try: connect_string = 'DSN=%(dsn)s;uid=%(user)s;pwd=%(password)s' % locals() conn = pyodbc.connect(connect_string, autocommit=True) cursor = conn.cursor() cursor.execute(sql) for row in cursor: print row except Exception as e: print e finally: cursor.close() conn.close() ########################################################### # MAIN ########################################################### if __name__ == '__main__': args = parse_args() execute_query('show databases', args.dsn, args.user, args.password) ... View more

@Ryan Chapin Looking for some suggestions. We have a query that was hanging indefinitely in Hive on Tez. We are running with HDP 2.4.0. After some debugging, we narrowed it down to a single ORC file in a Hive partition that contained that file plus about 10 others. If we move this one file out of the partition and test the query, it now completes. If we include ONLY that one file in the partition, the query hangs. Even a simple "select * from table" hangs. The query never gets beyond the first map task. I then discovered the ORC file dump feature of Hive and ran the following on this file: hive --orcfiledump --skip-dump --recover -d hdfs://vmwhaddev01:8020/tmp/000002_0 > orc.dump This command never returns to the command line and hangs, similar to what Hive does. I Tested this on a known good file and the dump completes successfully and returns control to the command line as expected. So, even this dump test is hanging. If I tail orc.dump during the hang, the last line of the file looks complete. So, I am wondering if this line is the end of a stripe and the next stripe is corrupt? The ORC reader seems to get into some infinite loop at this point. Once the dumped output file size stops increasing, at about 382 MB, top command shows the Hive dump process continuously using about 99.9% CPU until I CTRL+C the command. It dumps about 382 MB of data before the dump command hangs. Here's last line which is complete: {"_col0":"DSN001000325021","_col1":10784199,"_col2":1457431200,"_col3":1457434800,"_col4":20209,"_col5":0,"_col6":60,"_col7":10,"_col8":1456222331,"_col9":120,"_col10":117,"_col11":114,"_col12":0,"_col13":0,"_col14":0,"_col15":0,"_col16":0,"_col17":0,"_col18":120,"_col19":121,"_col20":123,"_col21":124,"_col22":125,"_col23":0,"_col24":0,"_col25":15296815,"_col26":2,"_col27":0,"_col28":163528,"_col29":88,"_col30":1498,"_col31":29082,"_col32":874908,"_col33":51565,"_col34":104138,"_col35":149,"_col36":6,"_col37":0,"_col38":0,"_col39":1508,"_col40":0,"_col41":2248,"_col42":46961,"_col43":624,"_col44":1732,"_col45":0,"_col46":0,"_col47":0,"_col48":0,"_col49":41,"_col50":159,"_col51":12,"_col52":30,"_col53":0,"_col54":0,"_col55":0,"_col56":0,"_col57":0,"_col58":0,"_col59":0,"_col60":0,"_col61":1398668,"_col62":5916915,"_col63":5916855,"_col64":5986115,"_col65":249,"_col66":66,"_col67":547,"_col68":76,"_col69":132618,"_col70":17398,"_col71":140325,"_col72":19012,"_col73":0,"_col74":0,"_col75":0,"_col76":0,"_col77":"TUC04HNSIGW63B002Adv","_col78":1456805959,"_col79":0,"_col80":158,"_col81":136,"_col82":0,"_col83":0,"_col84":1,"_col85":0,"_col86":12,"_col87":12,"_col88":0,"_col89":0,"_col90":0,"_col91":0,"_col92":12,"_col93":12,"_col94":0,"_col95":0,"_col96":0,"_col97":0,"_col98":18,"_col99":14,"_col100":0,"_col101":0,"_col102":0,"_col103":0,"_col104":12,"_col105":12,"_col106":0,"_col107":0,"_col108":0,"_col109":0,"_col110":51565,"_col111":37383,"_col112":0,"_col113":402,"_col114":0,"_col115":449,"_col116":3126,"_col117":46256,"_col118":28682,"_col119":4,"_col120":0,"_col121":0,"_col122":0,"_col123":0,"_col124":0,"_col125":0,"_col126":0,"_col127":0,"_col128":0,"_col129":0,"_col130":0,"_col131":0,"_col132":0,"_col133":0,"_col134":0,"_col135":0,"_col136":0,"_col137":0,"_col138":0,"_col139":0,"_col140":0,"_col141":0,"_col142":0,"_col143":"20.2.1-3569","_col144":0,"_col145":0,"_col146":0,"_col147":0,"_col148":0,"_col149":0,"_col150":0,"_col151":0,"_col152":0,"_col153":0,"_col154":0,"_col155":0,"_col156":0,"_col157":0,"_col158":0,"_col159":0,"_col160":0,"_col161":0,"_col162":0,"_col163":0,"_col164":0,"_col165":0,"_col166":0,"_col167":0,"_col168":0,"_col169":0,"_col170":0,"_col171":0,"_col172":0,"_col173":4,"_col174":"12-AUG-15","_col175":12,"_col176":"HT1100","_col177":"1","_col178":"B4WB16S2","_col179":"CORE_DSN_PROD_HT1100_50K","_col180":"SW_DSN_PROD_HT1100_50K","_col181":"3.2.0.24","_col182":"1000"} I am trying to determine if I have uncovered a bug or somehow the data that I am inserting into the ORC somehow resulted in this condition. Either way, it seems like a bug if you can insert data that causes an ORC file to become corrupted. The ingest pipeline for this data is as follows. I convert raw CSV files into Avro and land them in an HDFS directory. There could be multiple Avro schemas in play here as there are multiple versions of these CSV files in flight. The Avro schemas are designed such that I can include all versions in the same Hive table. Typically, newer versions of these stats files add more columns of stats. Once a day, I move all the Avro files that have accumulated to a temp directory and create an external table over the files in that directory I run a query that selects * from the external table and inserts all the results into another Hive managed table that is in ORC format, effectively using Hive to perform the Avro to ORC conversion. This query also performs a join with some data from one other table to enrich the data landing in the ORC table. This table is partitioned by year/month/day. Because the resulting ORC files are relatively small for HDFS, I perform one final step after the ORC insert query completes. I run a Hive query against the newly created partition to effectively compact the ORC files. Typically, the reduce part generates around 70 ORC files. I run a query like the following for the appropriate year, month, and day of the partition just created which typically compacts all 70 ORC files into about 5 much larger ones that are about 2-3 HDFS blocks (128 MB) in size each. alter table table_name partition (year=2016, month=3, day=16) concatenate; This is the first such issue we've seen in over two months of ingesting such files in this manner. Does anyone have any ideas of where to look further to possibly understand the root cause of this problem? Maybe the concatenate operation happened to cause the file corruption in this case? Anyone heard of such a thing? Should I file a bug report and provide this corrupt ORC file for some forensic analysis? I don't really want to start trying to hex dump and decode ORC to figure out what happened. ... View more