Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-657

How to create and work with a Hive JDBC connection?

    Details

    • Type: How To
    • Status: Published
    • Affects Version/s: EXASolution 5.0, EXASOL 6.0.0, Exasol 6.1
    • Fix Version/s: None
    • Labels:
      None
    • Environment:
      You have an EXASolution database and want to connect it via JDBC to Hive for using EXAloader IMPORT/EXPORT statements.
    • Solution:
      Hide

      Background

      You have an EXASolution database and want to connect it via JDBC to Hive for using EXAloader IMPORT/EXPORT statements.

      Prerequisites

      Choose one of the following drivers:

      How to create and work with a Hive JDBC connection?

      Step 1: Configuring the JDBC driver in EXAoperation:

       Step 2: Create or replace connection

      -- Connecting EXAloader via Cloudera Hive driver and Simba Hive driver to Cloudera, MapR and Hortonworks Hadoop distributions
      
      -- cloudera-quickstart-vm-5.13.0-0-vmware
      create or replace connection hive_conn to 'jdbc:hive2://192.168.42.133:10000' user 'cloudera' identified by 'cloudera';
      
      -- MapR-Sandbox-For-Hadoop-6.1.0-vmware
      create or replace connection hive_conn to 'jdbc:hive2://192.168.42.134:10000' user 'mapr' identified by 'mapr';
      
      -- Azure Hortonworks Sandbox with HDP 2.6.4
      create or replace connection hive_conn to 'jdbc:hive2://192.168.42.1:10000' user 'raj_ops' identified by 'raj_ops';
      

      Step 3: EXPORT/IMPORT

      EXPORT test for Cloudera and Simba driver

      export exa_syscat
          into jdbc driver = 'HiveCloudera' at hive_conn table exa_syscat created by
          'create table exa_syscat (schema_name varchar(128), object_name varchar(128), object_type varchar(15), object_comment varchar(2000))'
          replace;
      
      export exa_syscat
          into jdbc driver = 'HiveSimba' at hive_conn table exa_syscat created by
          'create table exa_syscat (schema_name varchar(128), object_name varchar(128), object_type varchar(15), object_comment varchar(2000))'
          replace;
      

      IMPORT test for Cloudera and Simba driver

      import into(schema_name varchar(128), object_name varchar(128), object_type varchar(15), object_comment varchar(2000))
          from jdbc driver = 'HiveCloudera' at hive_conn table exa_syscat;
      
      import into(schema_name varchar(128), object_name varchar(128), object_type varchar(15), object_comment varchar(2000))
          from jdbc driver = 'HiveSimba' at hive_conn table exa_syscat;
       
      Show
      Background You have an EXASolution database and want to connect it via JDBC to Hive for using EXAloader IMPORT/EXPORT statements. Prerequisites Choose one of the following drivers: Cloudera Hive JDBC driver: https://www.cloudera.com/downloads/connectors/hive/jdbc/2-6-5.html Simba Hive JDBC driver: https://www.simba.com/product/apache-hive-driver-with-sql-connector/?attribute_pa_license-type=evaluation&attribute_pa_os=java&attribute_pa_client-server=server How to create and work with a Hive JDBC connection? Step 1: Configuring the JDBC driver in EXAoperation:  Step 2: Create or replace connection -- Connecting EXAloader via Cloudera Hive driver and Simba Hive driver to Cloudera, MapR and Hortonworks Hadoop distributions -- cloudera-quickstart-vm-5.13.0-0-vmware create or replace connection hive_conn to 'jdbc:hive2://192.168.42.133:10000' user 'cloudera' identified by 'cloudera' ; -- MapR-Sandbox- For -Hadoop-6.1.0-vmware create or replace connection hive_conn to 'jdbc:hive2://192.168.42.134:10000' user 'mapr' identified by 'mapr' ; -- Azure Hortonworks Sandbox with HDP 2.6.4 create or replace connection hive_conn to 'jdbc:hive2://192.168.42.1:10000' user 'raj_ops' identified by 'raj_ops' ; Step 3: EXPORT/IMPORT EXPORT test for Cloudera and Simba driver export exa_syscat into jdbc driver = 'HiveCloudera' at hive_conn table exa_syscat created by ' create table exa_syscat ( schema_name varchar (128), object_name varchar (128), object_type varchar (15), object_comment varchar (2000))' replace ; export exa_syscat into jdbc driver = 'HiveSimba' at hive_conn table exa_syscat created by ' create table exa_syscat ( schema_name varchar (128), object_name varchar (128), object_type varchar (15), object_comment varchar (2000))' replace ; IMPORT test for Cloudera and Simba driver import into ( schema_name varchar (128), object_name varchar (128), object_type varchar (15), object_comment varchar (2000)) from jdbc driver = 'HiveCloudera' at hive_conn table exa_syscat; import into ( schema_name varchar (128), object_name varchar (128), object_type varchar (15), object_comment varchar (2000)) from jdbc driver = 'HiveSimba' at hive_conn table exa_syscat;  
    • Category 1:
      ETL / Data Integration - Virtual Schema
    • Category 2:
      Cluster Administration

      Attachments

        Activity

          People

          • Assignee:
            CaptainEXA Captain EXASOL
            Reporter:
            CaptainEXA Captain EXASOL
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: