Tips to identify yarn applications from resource manager for Hive Tez sessions

How To:

Use "hive.query.tag" at the connection string or set using "set hive.query.tag=usertag"

beeline -n hive -u jdbc:hive2://hdp:10000/default?hive.query.tag=donghuatag

Output:

[root@hdp ~]# yarn application -list -appTags donghuatag
19/08/06 21:52:30 INFO client.RMProxy: Connecting to ResourceManager at hdp.dbaglobe.com/192.168.31.22:8050
Total number of applications (application-types: [], states: [SUBMITTED, ACCEPTED, RUNNING] and tags: [donghuatag]):1
                Application-Id      Application-Name      Application-Type        User       Queue               State         Final-State         Progress                        Tracking-URL
application_1565096253064_0004  HIVE-7c5ec7e6-6944-483d-bf2d-b7a2d18ad7c1                  TEZ        hive     default             RUNNING           UNDEFINED               0%                http://hdp:41592/ui/

Query Kafka topic directly using hive-kafka-storagehandler

1. Create kafka topic

[donghua@hdp ~]$ /usr/hdp/current/kafka-broker/bin/kafka-topics.sh --create --zookeeper hdp:2181 --replication-factor 1 --partitions 1 --topic kafka_hive_topic
WARNING: Due to limitations in metric names, topics with a period ('.') or underscore ('_') could collide. To avoid issues it is best to use either, but not both.
Created topic "kafka_hive_topic".

2. Create hive table

[donghua@hdp ~]$ beeline -u "jdbc:hive2://hdp.dbaglobe.com:2181/demodb;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2" -n donghua -p x

Connecting to jdbc:hive2://hdp.dbaglobe.com:2181/demodb;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2

19/03/09 16:44:13 [main]: INFO jdbc.HiveConnection: Connected to hdp:10000

Connected to: Apache Hive (version 3.1.0.3.1.0.0-78)

Driver: Hive JDBC (version 3.1.0.3.1.0.0-78)

Transaction isolation: TRANSACTION_REPEATABLE_READ

Beeline version 3.1.0.3.1.0.0-78 by Apache Hive

0: jdbc:hive2://hdp.dbaglobe.com:2181/demodb> 

0: jdbc:hive2://hdp.dbaglobe.com:2181/demodb> CREATE EXTERNAL TABLE kafka_hive_table

. . . . . . . . . . . . . . . . . . . . . . >   (`Country Name` string , `Language` string,  `_id` struct<`$oid`:string>)

. . . . . . . . . . . . . . . . . . . . . . >   STORED BY 'org.apache.hadoop.hive.kafka.KafkaStorageHandler'

. . . . . . . . . . . . . . . . . . . . . . >   TBLPROPERTIES

. . . . . . . . . . . . . . . . . . . . . . >   ("kafka.topic" = "kafka_hive_topic", "kafka.bootstrap.servers"="hdp:6667");

No rows affected (4.747 seconds)

0: jdbc:hive2://hdp.dbaglobe.com:2181/demodb> desc kafka_hive_table;

+---------------+----------------------+--------------------+

|   col_name    |      data_type       |      comment       |

+---------------+----------------------+--------------------+

| country name  | string               | from deserializer  |

| language      | string               | from deserializer  |

| _id           | struct<$oid:string>  | from deserializer  |

| __key         | binary               | from deserializer  |

| __partition   | int                  | from deserializer  |

| __offset      | bigint               | from deserializer  |

| __timestamp   | bigint               | from deserializer  |

+---------------+----------------------+--------------------+

7 rows selected (0.359 seconds)

0: jdbc:hive2://hdp.dbaglobe.com:2181/demodb> !outputformat tsv2

0: jdbc:hive2://hdp.dbaglobe.com:2181/demodb> !brief

verbose: off

createtab_stmt

CREATE EXTERNAL TABLE `kafka_hive_table`(

  `country name` string COMMENT 'from deserializer', 

  `language` string COMMENT 'from deserializer', 

  `_id` struct<$oid:string> COMMENT 'from deserializer', 

  `__key` binary COMMENT 'from deserializer', 

  `__partition` int COMMENT 'from deserializer', 

  `__offset` bigint COMMENT 'from deserializer', 

  `__timestamp` bigint COMMENT 'from deserializer')

ROW FORMAT SERDE 

  'org.apache.hadoop.hive.kafka.KafkaSerDe' 

STORED BY 

  'org.apache.hadoop.hive.kafka.KafkaStorageHandler' 

WITH SERDEPROPERTIES ( 

  'serialization.format'='1')

LOCATION

  'hdfs://hdp.dbaglobe.com:8020/warehouse/tablespace/external/hive/demodb.db/kafka_hive_table'

TBLPROPERTIES (

  'bucketing_version'='2', 

  'hive.kafka.max.retries'='6', 

  'hive.kafka.metadata.poll.timeout.ms'='30000', 

  'hive.kafka.optimistic.commit'='false', 

  'hive.kafka.poll.timeout.ms'='5000', 

  'kafka.bootstrap.servers'='hdp:6667', 

  'kafka.serde.class'='org.apache.hadoop.hive.serde2.JsonSerDe', 

  'kafka.topic'='kafka_hive_topic', 

  'kafka.write.semantic'='AT_LEAST_ONCE', 

  'transient_lastDdlTime'='1552121132')

27 rows selected (0.109 seconds)

0: jdbc:hive2://hdp.dbaglobe.com:2181/demodb> 

3. Ingest some data into Kafka topic

[donghua@hdp ~]$ /usr/hdp/current/kafka-broker/bin/kafka-console-producer.sh --broker-list hdp:6667 --topic kafka_hive_topic

>{"Country Name":"Afrika","Language":"af","_id":{"$oid":"55a0f1d420a4d760b5fbdbd6"},"ISO":0}

>{"Country Name":"Oseanië","Language":"af","_id":{"$oid":"55a0f1d420a4d760b5fbdbd7"},"ISO":0}

>^C

[donghua@hdp ~]$ /usr/hdp/current/kafka-broker/bin/kafka-console-consumer.sh --topic kafka_hive_topic --bootstrap-server hdp:6667 --from-beginning

{"Country Name":"Afrika","Language":"af","_id":{"$oid":"55a0f1d420a4d760b5fbdbd6"},"ISO":0}

{"Country Name":"Oseanië","Language":"af","_id":{"$oid":"55a0f1d420a4d760b5fbdbd7"},"ISO":0}

^C

Processed a total of 2 messages

4. Query hive table

0: jdbc:hive2://hdp.dbaglobe.com:2181/demodb> select t.`Country Name` as Name, t.`Language` as lang, t.`__offset` from kafka_hive_table t;

INFO  : Compiling command(queryId=hive_20190309175638_f3a01692-28be-4683-bc66-32854615782c): select t.`Country Name` as Name, t.`Language` as lang, t.`__offset` from kafka_hive_table t

INFO  : Semantic Analysis Completed (retrial = false)

INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:name, type:string, comment:null), FieldSchema(name:lang, type:string, comment:null), FieldSchema(name:t.__offset, type:bigint, comment:null)], properties:null)

INFO  : Completed compiling command(queryId=hive_20190309175638_f3a01692-28be-4683-bc66-32854615782c); Time taken: 0.289 seconds

INFO  : Executing command(queryId=hive_20190309175638_f3a01692-28be-4683-bc66-32854615782c): select t.`Country Name` as Name, t.`Language` as lang, t.`__offset` from kafka_hive_table t

INFO  : Completed executing command(queryId=hive_20190309175638_f3a01692-28be-4683-bc66-32854615782c); Time taken: 0.006 seconds

INFO  : OK

+----------+-------+-------------+

|   name   | lang  | t.__offset  |

+----------+-------+-------------+

| Afrika   | af    | 12          |

| Oseanië  | af    | 13          |

+----------+-------+-------------+

2 rows selected (0.379 seconds)

Additional Finding

if empty messages inside Kafka topic, the hive result could be wrong, as below:

offset 3 and 4 are empty string

Row 3 and 4 are incorrect, which repeat data from row 2.

How to import Hive metadata to Atlas

How to import existing hive tables which created before Apache Atlas added?

[hive@hdp ~]$ /usr/hdp/current/atlas-server/hook-bin/import-hive.sh
Using Hive configuration directory [/etc/hive/conf]
Log file for import is /usr/hdp/current/atlas-server/logs/import-hive.log
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/hdp/3.1.0.0-78/hive/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/3.1.0.0-78/hadoop/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
2019-02-28T18:20:05,890 INFO [main] org.apache.atlas.ApplicationProperties - Looking for atlas-application.properties in classpath
2019-02-28T18:20:05,897 INFO [main] org.apache.atlas.ApplicationProperties - Loading atlas-application.properties from file:/etc/hive/3.1.0.0-78/0/atlas-application.properties
2019-02-28T18:20:06,006 INFO [main] org.apache.atlas.ApplicationProperties - No graphdb backend specified. Will use 'janus'
2019-02-28T18:20:06,006 INFO [main] org.apache.atlas.ApplicationProperties - Using storage backend 'hbase2'
2019-02-28T18:20:06,006 INFO [main] org.apache.atlas.ApplicationProperties - Using index backend 'solr'
2019-02-28T18:20:06,007 INFO [main] org.apache.atlas.ApplicationProperties - Setting solr-wait-searcher property 'true'
2019-02-28T18:20:06,007 INFO [main] org.apache.atlas.ApplicationProperties - Setting index.search.map-name property 'false'
2019-02-28T18:20:06,011 INFO [main] org.apache.atlas.ApplicationProperties - Property (set to default) atlas.graph.cache.db-cache = true
2019-02-28T18:20:06,011 INFO [main] org.apache.atlas.ApplicationProperties - Property (set to default) atlas.graph.cache.db-cache-clean-wait = 20
2019-02-28T18:20:06,012 INFO [main] org.apache.atlas.ApplicationProperties - Property (set to default) atlas.graph.cache.db-cache-size = 0.5
2019-02-28T18:20:06,012 INFO [main] org.apache.atlas.ApplicationProperties - Property (set to default) atlas.graph.cache.tx-cache-size = 15000
2019-02-28T18:20:06,012 INFO [main] org.apache.atlas.ApplicationProperties - Property (set to default) atlas.graph.cache.tx-dirty-size = 120
Enter username for atlas :- admin
Enter password for atlas :- 
2019-02-28T18:20:10,984 INFO [main] org.apache.atlas.AtlasBaseClient - Client has only one service URL, will use that for all actions: http://hdp.dbaglobe.com:21000
2019-02-28T18:20:11,028 INFO [main] org.apache.hadoop.hive.conf.HiveConf - Found configuration file file:/etc/hive/3.1.0.0-78/0/hive-site.xml
2019-02-28T18:20:12,131 WARN [main] org.apache.hadoop.hive.conf.HiveConf - HiveConf of name hive.stats.fetch.partition.stats does not exist
2019-02-28T18:20:12,131 WARN [main] org.apache.hadoop.hive.conf.HiveConf - HiveConf of name hive.heapsize does not exist
2019-02-28T18:20:13,617 WARN [main] org.apache.hadoop.util.NativeCodeLoader - Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
2019-02-28T18:20:14,051 INFO [main] org.apache.hadoop.hive.metastore.HiveMetaStoreClient - Trying to connect to metastore with URI thrift://hdp:9083
2019-02-28T18:20:14,223 INFO [main] org.apache.hadoop.hive.metastore.HiveMetaStoreClient - Opened a connection to metastore, current connections: 1
2019-02-28T18:20:14,474 INFO [main] org.apache.hadoop.hive.metastore.HiveMetaStoreClient - Connected to metastore.
2019-02-28T18:20:14,474 INFO [main] org.apache.hadoop.hive.metastore.RetryingMetaStoreClient - RetryingMetaStoreClient proxy=class org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient ugi=hive (auth:SIMPLE) retries=24 delay=5 lifetime=0
2019-02-28T18:20:15,314 INFO [main] org.apache.atlas.hive.bridge.HiveMetaStoreBridge - Importing Hive metadata
2019-02-28T18:20:15,356 INFO [main] org.apache.atlas.hive.bridge.HiveMetaStoreBridge - Found 4 databases
2019-02-28T18:20:15,717 INFO [main] org.apache.atlas.AtlasBaseClient - method=GET path=api/atlas/v2/entity/uniqueAttribute/type/ contentType=application/json; charset=UTF-8 accept=application/json status=200
2019-02-28T18:20:16,494 INFO [main] org.apache.atlas.hive.bridge.HiveMetaStoreBridge - Database default is already registered - id=620ebb57-a216-469f-8184-3def1b22da16. Updating it.
2019-02-28T18:20:16,711 INFO [main] org.apache.atlas.AtlasBaseClient - method=POST path=api/atlas/v2/entity/ contentType=application/json; charset=UTF-8 accept=application/json status=200
2019-02-28T18:20:16,815 INFO [main] org.apache.atlas.hive.bridge.HiveMetaStoreBridge - Updated hive_db entity: name=default@lake, guid=620ebb57-a216-469f-8184-3def1b22da16
2019-02-28T18:20:16,848 INFO [main] org.apache.atlas.hive.bridge.HiveMetaStoreBridge - No tables to import in database default
2019-02-28T18:20:16,894 INFO [main] org.apache.atlas.AtlasBaseClient - method=GET path=api/atlas/v2/entity/uniqueAttribute/type/ contentType=application/json; charset=UTF-8 accept=application/json status=200
2019-02-28T18:20:16,896 INFO [main] org.apache.atlas.hive.bridge.HiveMetaStoreBridge - Database demodb is already registered - id=2ebf0dce-e258-4121-ac3f-16e38facce2e. Updating it.
2019-02-28T18:20:16,923 INFO [main] org.apache.atlas.AtlasBaseClient - method=POST path=api/atlas/v2/entity/ contentType=application/json; charset=UTF-8 accept=application/json status=200
2019-02-28T18:20:16,923 INFO [main] org.apache.atlas.hive.bridge.HiveMetaStoreBridge - Updated hive_db entity: name=demodb@lake, guid=2ebf0dce-e258-4121-ac3f-16e38facce2e
2019-02-28T18:20:16,928 INFO [main] org.apache.atlas.hive.bridge.HiveMetaStoreBridge - Found 5 tables to import in database demodb
2019-02-28T18:20:17,184 INFO [main] org.apache.atlas.AtlasBaseClient - method=GET path=api/atlas/v2/entity/uniqueAttribute/type/ contentType=application/json; charset=UTF-8 accept=application/json status=404
2019-02-28T18:20:17,234 WARN [main] org.apache.hadoop.hive.conf.HiveConf - HiveConf of name hive.stats.fetch.partition.stats does not exist
2019-02-28T18:20:17,234 WARN [main] org.apache.hadoop.hive.conf.HiveConf - HiveConf of name hive.heapsize does not exist
2019-02-28T18:20:23,463 INFO [main] org.apache.atlas.AtlasBaseClient - method=POST path=api/atlas/v2/entity/ contentType=application/json; charset=UTF-8 accept=application/json status=200
2019-02-28T18:20:23,604 INFO [main] org.apache.atlas.AtlasBaseClient - method=GET path=api/atlas/v2/entity/guid/ contentType=application/json; charset=UTF-8 accept=application/json status=200
2019-02-28T18:20:23,607 INFO [main] org.apache.atlas.hive.bridge.HiveMetaStoreBridge - Created hive_table entity: name=demodb.person@lake, guid=af210ef7-9c1c-4faf-b07e-363dc51683aa
2019-02-28T18:20:23,637 INFO [main] org.apache.atlas.AtlasBaseClient - method=GET path=api/atlas/v2/entity/uniqueAttribute/type/ contentType=application/json; charset=UTF-8 accept=application/json status=404
2019-02-28T18:20:25,496 INFO [main] org.apache.atlas.AtlasBaseClient - method=POST path=api/atlas/v2/entity/ contentType=application/json; charset=UTF-8 accept=application/json status=200
2019-02-28T18:20:25,573 INFO [main] org.apache.atlas.AtlasBaseClient - method=GET path=api/atlas/v2/entity/guid/ contentType=application/json; charset=UTF-8 accept=application/json status=200
2019-02-28T18:20:25,575 INFO [main] org.apache.atlas.hive.bridge.HiveMetaStoreBridge - Created hive_table entity: name=demodb.zip@lake, guid=8aad59c6-ed05-43c9-8d89-e74ac712ee2b
2019-02-28T18:20:25,670 INFO [main] org.apache.atlas.AtlasBaseClient - method=GET path=api/atlas/v2/entity/uniqueAttribute/type/ contentType=application/json; charset=UTF-8 accept=application/json status=200
2019-02-28T18:20:25,673 INFO [main] org.apache.atlas.hive.bridge.HiveMetaStoreBridge - Table demodb.position is already registered with id 5476f1f0-8175-4b50-873b-e6589d0313bf. Updating entity.
2019-02-28T18:20:26,268 INFO [main] org.apache.atlas.AtlasBaseClient - method=POST path=api/atlas/v2/entity/ contentType=application/json; charset=UTF-8 accept=application/json status=200

 ...

2019-02-28T18:21:45,219 INFO [main] org.apache.atlas.hive.bridge.HiveMetaStoreBridge - Updated hive_table entity: name=sys.wm_mappings@lake, guid=505dd84a-0221-433d-bc9e-5a406062a48c

2019-02-28T18:21:45,232 INFO [main] org.apache.atlas.AtlasBaseClient - method=GET path=api/atlas/v2/entity/uniqueAttribute/type/ contentType=application/json; charset=UTF-8 accept=application/json status=200

2019-02-28T18:21:45,232 INFO [main] org.apache.atlas.hive.bridge.HiveMetaStoreBridge - Process sys.wm_mappings@lake:1549277088000 is already registered

2019-02-28T18:21:45,232 INFO [main] org.apache.atlas.hive.bridge.HiveMetaStoreBridge - Successfully imported 45 tables from database sys

Hive Meta Data imported successfully!!!

Workaround using Hive+Sentry without Kerberos/LDAP

[donghua@cdh01 ~]$ beeline -u jdbc:hive2://cdh01:10000/testdb
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
scan complete in 3ms
Connecting to jdbc:hive2://cdh01:10000/testdb
Connected to: Apache Hive (version 1.1.0-cdh5.14.2)
Driver: Hive JDBC (version 1.1.0-cdh5.14.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.1.0-cdh5.14.2 by Apache Hive
0: jdbc:hive2://cdh01:10000/testdb> select current_user();
Error: Error while compiling statement: FAILED: InvalidConfigurationException hive.server2.authentication can't be none in non-testing mode (state=42000,code=40000)
0: jdbc:hive2://cdh01:10000/testdb>

[HiveServer2-Handler-Pool: Thread-58]: FAILED: InvalidConfigurationException hive.server2.authentication can't be none in non-testing mode
org.apache.sentry.binding.hive.conf.InvalidConfigurationException: hive.server2.authentication can't be none in non-testing mode
    at org.apache.sentry.binding.hive.authz.HiveAuthzBinding.validateHiveServer2Config(HiveAuthzBinding.java:180)
    at org.apache.sentry.binding.hive.authz.HiveAuthzBinding.validateHiveConfig(HiveAuthzBinding.java:148)
    at org.apache.sentry.binding.hive.authz.HiveAuthzBinding.<init>(HiveAuthzBinding.java:84)
    at org.apache.sentry.binding.hive.authz.HiveAuthzBinding.<init>(HiveAuthzBinding.java:80)
    at org.apache.sentry.binding.hive.HiveAuthzBindingHook.<init>(HiveAuthzBindingHook.java:121)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at java.lang.Class.newInstance(Class.java:442)
    at org.apache.hadoop.hive.ql.hooks.HooksLoader.getHooks(HooksLoader.java:100)
    at org.apache.hadoop.hive.ql.hooks.HooksLoader.getHooks(HooksLoader.java:64)

[donghua@cdh01 ~]$ beeline -n donghua -u jdbc:hive2://cdh01:10000/testdb
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
scan complete in 2ms
Connecting to jdbc:hive2://cdh01:10000/testdb
Connected to: Apache Hive (version 1.1.0-cdh5.14.2)
Driver: Hive JDBC (version 1.1.0-cdh5.14.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.1.0-cdh5.14.2 by Apache Hive
0: jdbc:hive2://cdh01:10000/testdb> select current_user();
INFO  : Compiling command(queryId=hive_20180526200404_ef3e8dd5-4281-4338-b85d-386db2122937): select current_user()
INFO  : Semantic Analysis Completed
INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:_c0, type:string, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=hive_20180526200404_ef3e8dd5-4281-4338-b85d-386db2122937); Time taken: 0.132 seconds
INFO  : Executing command(queryId=hive_20180526200404_ef3e8dd5-4281-4338-b85d-386db2122937): select current_user()
INFO  : Completed executing command(queryId=hive_20180526200404_ef3e8dd5-4281-4338-b85d-386db2122937); Time taken: 0.0 seconds
INFO  : OK
+-------+--+
|  _c0  |
+-------+--+
| hive  |
+-------+--+
1 row selected (0.261 seconds)

Regexp_extract example syntax explained in Hive

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select '${hivevar:str1}';
+-----------------------+--+
|          _c0          |
+-----------------------+--+
| adfsdf1231asdf123123  |
+-----------------------+--+
1 row selected (0.115 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select regexp_extract('${hivevar:str1}','(\\d+)\\D*(\\d+)',1) part1,regexp_extract('${hivevar:str1}','(\\d+)\\D*(\\d+)',2) part2;
+--------+---------+--+
| part1  |  part2  |
+--------+---------+--+
| 1231   | 123123  |
+--------+---------+--+
1 row selected (0.067 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> desc function regexp_extract;
+----------------------------------------------------+--+
|                      tab_name                      |
+----------------------------------------------------+--+
| regexp_extract(str, regexp[, idx]) - extracts a group that matches regexp |
+----------------------------------------------------+--+
1 row selected (0.029 seconds)

Substitute variables in Hive

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> set hivevar:zipcode=42031;
No rows affected (0.007 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select fname,lname from employees where zipcode='${hivevar:zipcode}';
+--------+-----------+--+
| fname  |   lname   |
+--------+-----------+--+
| Eliza  | Weller    |
| Russ   | Jennings  |
+--------+-----------+--+
2 rows selected (22.079 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> set hivevar:zipcode=95066;
No rows affected (0.011 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select fname,lname from employees where zipcode='${hivevar:zipcode}';
+--------------+-------------+--+
|    fname     |    lname    |
+--------------+-------------+--+
| Anna         | Atkins      |
| Austin       | Dickson     |
| Alex         | Magee       |
| Alan         | Millner     |
| Ashley       | Quarles     |


[donghua@cdh-vm ~]$ beeline -u jdbc:hive2://cdh-vm.dbaglobe.com:10000 -n donghua --hivevar zipcode="42031"

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> set hivevar:zipcode;
+------------------------+--+
|          set           |
+------------------------+--+
| hivevar:zipcode=42031  |
+------------------------+--+
1 row selected (0.022 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select fname,lname from employees where zipcode='${hivevar:zipcode}';
+--------+-----------+--+
| fname  |   lname   |
+--------+-----------+--+
| Eliza  | Weller    |
| Russ   | Jennings  |
+--------+-----------+--+
2 rows selected (20.247 seconds)

Construct SCD Type 2 in Hive examples

create table products(prod_id int, name string, price double, last_modified timestamp) stored as parquet;
create table orders(order_id int, prod_id int, quantity int, order_date timestamp) stored as parquet;

insert into products values(1,'Soap',1.0,'2017-01-01');
insert into products values(1,'Soap',1.1,'2017-04-01');
insert into products values(1,'Soap',1.2,'2017-07-01');
insert into products values(1,'Soap',1.3,'2017-10-01');
insert into products values(2,'Soda',0.6,'2017-01-01');
insert into products values(2,'Soda',0.8,'2017-06-01');
insert into products values(3,'Beer',4.2,'2017-07-01');

insert into orders values(1,1,1,'2017-02-10');
insert into orders values(2,2,10,'2017-07-10');
insert into orders values(3,3,5,'2017-09-10');
insert into orders values(4,1,2,'2017-12-01');

create view prod_scd2 as 
select prod_id,price,last_modified as valid_from,
lead(last_modified) over (partition by prod_id order by last_modified) as valid_end
from products;

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select * from prod_scd2 p;
+------------+----------+------------------------+------------------------+--+
| p.prod_id  | p.price  |      p.valid_from      |      p.valid_end       |
+------------+----------+------------------------+------------------------+--+
| 1          | 1.0      | 2017-01-01 00:00:00.0  | 2017-04-01 00:00:00.0  |
| 1          | 1.1      | 2017-04-01 00:00:00.0  | 2017-07-01 00:00:00.0  |
| 1          | 1.2      | 2017-07-01 00:00:00.0  | 2017-10-01 00:00:00.0  |
| 1          | 1.3      | 2017-10-01 00:00:00.0  | NULL                   |
| 2          | 0.6      | 2017-01-01 00:00:00.0  | 2017-06-01 00:00:00.0  |
| 2          | 0.8      | 2017-06-01 00:00:00.0  | NULL                   |
| 3          | 4.2      | 2017-07-01 00:00:00.0  | NULL                   |
+------------+----------+------------------------+------------------------+--+
7 rows selected (20.253 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select * from orders o;
+-------------+------------+-------------+------------------------+--+
| o.order_id  | o.prod_id  | o.quantity  |      o.order_date      |
+-------------+------------+-------------+------------------------+--+
| 1           | 1          | 1           | 2017-02-10 00:00:00.0  |
| 2           | 2          | 10          | 2017-07-10 00:00:00.0  |
| 3           | 3          | 5           | 2017-09-10 00:00:00.0  |
| 4           | 1          | 2           | 2017-12-01 00:00:00.0  |
+-------------+------------+-------------+------------------------+--+
4 rows selected (0.152 seconds)

select o.order_id,o.order_date,p.price,o.quantity*p.price as total_cost
from orders o join prod_scd2 p
on (o.prod_id=p.prod_id) 
where o.order_date between p.valid_from and if(p.valid_end is not null ,p.valid_end,cast('9999-12-31' as timestamp))
order by order_id;

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select o.order_id,o.order_date,p.price,o.quantity*p.price as total_cost
. . . . . . . . . . . . . . . . . . . . .> from orders o join prod_scd2 p
. . . . . . . . . . . . . . . . . . . . .> on (o.prod_id=p.prod_id) 
. . . . . . . . . . . . . . . . . . . . .> where o.order_date between p.valid_from and if(p.valid_end is not null ,p.valid_end,cast('9999-12-31' as timestamp))
. . . . . . . . . . . . . . . . . . . . .> order by order_id;

+-------------+------------------------+----------+-------------+--+
| o.order_id  |      o.order_date      | p.price  | total_cost  |
+-------------+------------------------+----------+-------------+--+
| 1           | 2017-02-10 00:00:00.0  | 1.0      | 1.0         |
| 2           | 2017-07-10 00:00:00.0  | 0.8      | 8.0         |
| 3           | 2017-09-10 00:00:00.0  | 4.2      | 21.0        |
| 4           | 2017-12-01 00:00:00.0  | 1.3      | 2.6         |
+-------------+------------------------+----------+-------------+--+
4 rows selected (48.862 seconds)

Hive rename table column on Avro data format

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> create table t1 (id1 int) stored as avro;

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> insert into t1 values(1);

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> insert into t1 values(2);

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> alter table t1 change id1 id2 int;

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> insert into t1 values(3);

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select * from t1;
+---------+--+
| t1.id2  |
+---------+--+
| NULL    |
| NULL    |
| 3       |
+---------+--+
3 rows selected (0.135 seconds)

[donghua@cdh-vm ~]$ hdfs dfs -cat /user/hive/warehouse/t1/000000_0
Objavro.schema?{"type":"record","name":"t1","namespace":"default","fields":[{"name":"id1","type":["null","int"],"default":null}]}p??&T??z???Ӌ??&T??z???Ӌ

[donghua@cdh-vm ~]$ hdfs dfs -cat /user/hive/warehouse/t1/000000_0_copy_1
Objavro.schema?{"type":"record","name":"t1","namespace":"default","fields":[{"name":"id1","type":["null","int"],"default":null}]}?0??Qs>?4?????0??Qs>?4????

[donghua@cdh-vm ~]$ hdfs dfs -cat /user/hive/warehouse/t1/000000_0_copy_2
Objavro.schema?{"type":"record","name":"t1","namespace":"default","fields":[{"name":"id2","type":["null","int"],"default":null}]}>?>?)x?Dv&?I??1>?>?)x?Dv&?I??1

Impala can't insert on Avro format

https://www.cloudera.com/documentation/enterprise/5-6-x/topics/impala_avro.html

Impala supports using tables whose data files use the Avro file format. Impala can query Avro tables, and in Impala 1.4.0 and higher can create them, but currently cannot insert data into them. For insert operations, use Hive, then switch back to Impala to run queries.


[cdh-vm.dbaglobe.com:21000] > create table t1(id1 int) stored as avro;
Query: create table t1(id1 int) stored as avro
Fetched 0 row(s) in 1.59s

[cdh-vm.dbaglobe.com:21000] > insert into t1 values(1);
Query: insert into t1 values(1)
Query submitted at: 2018-03-02 21:45:36 (Coordinator: http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=6847cc676e330ec0:c1d2b6d300000000
WARNINGS: Writing to table format AVRO is not supported. Use query option ALLOW_UNSUPPORTED_FORMATS to override.


[cdh-vm.dbaglobe.com:21000] > select * from t1;
Query: select * from t1
Query submitted at: 2018-03-02 21:46:00 (Coordinator: http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=974e823123b8e334:bec6f70600000000
Fetched 0 row(s) in 0.06s

[cdh-vm.dbaglobe.com:21000] > set ALLOW_UNSUPPORTED_FORMATS=true;
ALLOW_UNSUPPORTED_FORMATS set to true

[cdh-vm.dbaglobe.com:21000] > insert into t1 values(1);
Query: insert into t1 values(1)
Query submitted at: 2018-03-02 21:47:23 (Coordinator: http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=f148965b772acd2e:fa4afc8700000000
Modified 0 row(s) in 1.04s

[cdh-vm.dbaglobe.com:21000] > select * from t1;
Query: select * from t1
Query submitted at: 2018-03-02 21:47:26 (Coordinator: http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=3c4ed8a0106b5cd1:6a45ee0c00000000
+------+
| id1  |
+------+
| NULL |
+------+
Fetched 1 row(s) in 0.36s

[donghua@cdh-vm temp]$ beeline -u jdbc:hive2://cdh-vm.dbaglobe.com:10000 -n donghua
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
scan complete in 2ms
Connecting to jdbc:hive2://cdh-vm.dbaglobe.com:10000
Connected to: Apache Hive (version 1.1.0-cdh5.14.0)
Driver: Hive JDBC (version 1.1.0-cdh5.14.0)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.1.0-cdh5.14.0 by Apache Hive

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select * from t1;
Error: java.io.IOException: org.apache.avro.AvroRuntimeException: java.io.IOException: Checksum failure (state=,code=0)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> truncate table t1;
ERROR : FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Exception while processing
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Exception while processing (state=08S01,code=1)

-- From Impala
[cdh-vm.dbaglobe.com:21000] > drop table t1;
Query: drop table t1

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> create table t1 (id1 int) stored as avro;
No rows affected (0.136 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> insert into t1 values(1);

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000> select * from t1;
+---------+--+
| t1.id1  |
+---------+--+
| 1       |
+---------+--+

Complex data process in SQL vs Pig

[donghua@cdh-vm temp]$ hcat -e "desc employees.departments"
dept_no             string                                  
dept_name           string                                  

[donghua@cdh-vm temp]$ hcat -e "desc employees.dept_manager"
emp_no               int                                    
dept_no             string                                  
from_date           string                                  
to_date             string                                  


[donghua@cdh-vm temp]$ hcat -e "desc employees.dept_emp"
emp_no               int                                    
dept_no             string                                  
from_date           string                                  
to_date             string                                  

[donghua@cdh-vm temp]$ hcat -e "desc employees.employees"
emp_no               int                                    
birth_date           string                                  
first_name           string                                  
last_name           string                                  
gender               string                                  
hire_date           string                                  


-- Find out their manager name & department size
select d.dept_name, concat(m.first_name,' ',m.last_name) as manager, count(e.emp_no) employees
from employees.departments d 
join employees.dept_manager dm on d.dept_no = dm.dept_no
join employees.employees m on dm.emp_no = m.emp_no
join employees.dept_emp de on d.dept_no = de.dept_no
join employees.employees e on de.emp_no = e.emp_no
where de.to_date >'2018-01-01'
and dm.to_date > '2018-01-01'
group by d.dept_name, concat(m.first_name,' ',m.last_name)
order by d.dept_name;

Run SQL in Hive:

Connecting to jdbc:hive2://cdh-vm.dbaglobe.com:10000/employees
Connected to: Apache Hive (version 1.1.0-cdh5.14.0)
Driver: Hive JDBC (version 1.1.0-cdh5.14.0)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.1.0-cdh5.14.0 by Apache Hive
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/emp> -- Find out their manager name & department size
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/emp> select d.dept_name, concat(m.first_name,' ',m.last_name) as manager, count(e.emp_no) employees
. . . . . . . . . . . . . . . . . . . . . . .> from employees.departments d 
. . . . . . . . . . . . . . . . . . . . . . .> join employees.dept_manager dm on d.dept_no = dm.dept_no
. . . . . . . . . . . . . . . . . . . . . . .> join employees.employees m on dm.emp_no = m.emp_no
. . . . . . . . . . . . . . . . . . . . . . .> join employees.dept_emp de on d.dept_no = de.dept_no
. . . . . . . . . . . . . . . . . . . . . . .> join employees.employees e on de.emp_no = e.emp_no
. . . . . . . . . . . . . . . . . . . . . . .> where de.to_date >'2018-01-01'
. . . . . . . . . . . . . . . . . . . . . . .> and dm.to_date > '2018-01-01'
. . . . . . . . . . . . . . . . . . . . . . .> group by d.dept_name, concat(m.first_name,' ',m.last_name)
. . . . . . . . . . . . . . . . . . . . . . .> order by d.dept_name;

+---------------------+--------------------+------------+--+
|     d.dept_name     |      manager       | employees  |
+---------------------+--------------------+------------+--+
| Customer Service    | Yuchang Weedman    | 17569      |
| Development         | Leon DasSarma      | 61386      |
| Finance             | Isamu Legleitner   | 12437      |
| Human Resources     | Karsten Sigstam    | 12898      |
| Marketing           | Vishwani Minakawa  | 14842      |
| Production          | Oscar Ghazalie     | 53304      |
| Quality Management  | Dung Pesch         | 14546      |
| Research            | Hilary Kambil      | 15441      |
| Sales               | Hauke Zhang        | 37701      |
+---------------------+--------------------+------------+--+
9 rows selected (100.528 seconds)

Run SQL in Impala:

Connected to cdh-vm.dbaglobe.com:21000
Server version: impalad version 2.11.0-cdh5.14.0 RELEASE (build d68206561bce6b26762d62c01a78e6cd27aa7690)
***********************************************************************************
Welcome to the Impala shell.
(Impala Shell v2.11.0-cdh5.14.0 (d682065) built on Sat Jan  6 13:27:16 PST 2018)

Press TAB twice to see a list of available commands.
***********************************************************************************
[cdh-vm.dbaglobe.com:21000] > -- Find out their manager name & department size
                            > select d.dept_name, concat(m.first_name,' ',m.last_name) as manager, count(e.emp_no) employees
                            > from employees.departments d 
                            > join employees.dept_manager dm on d.dept_no = dm.dept_no
                            > join employees.employees m on dm.emp_no = m.emp_no
                            > join employees.dept_emp de on d.dept_no = de.dept_no
                            > join employees.employees e on de.emp_no = e.emp_no
                            > where de.to_date >'2018-01-01'
                            > and dm.to_date > '2018-01-01'
                            > group by d.dept_name, concat(m.first_name,' ',m.last_name)
                            > order by d.dept_name;
Query: -- Find out their manager name & department size
select d.dept_name, concat(m.first_name,' ',m.last_name) as manager, count(e.emp_no) employees
from employees.departments d
join employees.dept_manager dm on d.dept_no = dm.dept_no
join employees.employees m on dm.emp_no = m.emp_no
join employees.dept_emp de on d.dept_no = de.dept_no
join employees.employees e on de.emp_no = e.emp_no
where de.to_date >'2018-01-01'
and dm.to_date > '2018-01-01'
group by d.dept_name, concat(m.first_name,' ',m.last_name)
order by d.dept_name
Query submitted at: 2018-02-18 20:58:51 (Coordinator: http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=a04e8317637c0e4a:a83017f00000000
+--------------------+-------------------+-----------+
| dept_name          | manager           | employees |
+--------------------+-------------------+-----------+
| Customer Service   | Yuchang Weedman   | 17569     |
| Development        | Leon DasSarma     | 61386     |
| Finance            | Isamu Legleitner  | 12437     |
| Human Resources    | Karsten Sigstam   | 12898     |
| Marketing          | Vishwani Minakawa | 14842     |
| Production         | Oscar Ghazalie    | 53304     |
| Quality Management | Dung Pesch        | 14546     |
| Research           | Hilary Kambil     | 15441     |
| Sales              | Hauke Zhang       | 37701     |
+--------------------+-------------------+-----------+
Fetched 9 row(s) in 19.43s

Run SQL in Mysql:
  
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [employees]> -- Find out their manager name & department size
MariaDB [employees]> select d.dept_name, concat(m.first_name,' ',m.last_name) as manager, count(e.emp_no) employees
    -> from employees.departments d 
    -> join employees.dept_manager dm on d.dept_no = dm.dept_no
    -> join employees.employees m on dm.emp_no = m.emp_no
    -> join employees.dept_emp de on d.dept_no = de.dept_no
    -> join employees.employees e on de.emp_no = e.emp_no
    -> where de.to_date >'2018-01-01'
    -> and dm.to_date > '2018-01-01'
    -> group by d.dept_name, concat(m.first_name,' ',m.last_name)
    -> order by d.dept_name;
+--------------------+-------------------+-----------+
| dept_name          | manager           | employees |
+--------------------+-------------------+-----------+
| Customer Service   | Yuchang Weedman   |     17569 |
| Development        | Leon DasSarma     |     61386 |
| Finance            | Isamu Legleitner  |     12437 |
| Human Resources    | Karsten Sigstam   |     12898 |
| Marketing          | Vishwani Minakawa |     14842 |
| Production         | Oscar Ghazalie    |     53304 |
| Quality Management | Dung Pesch        |     14546 |
| Research           | Hilary Kambil     |     15441 |
| Sales              | Hauke Zhang       |     37701 |
+--------------------+-------------------+-----------+
9 rows in set (1.62 sec)

Re-write SQL in Pig-Latin:

-- pig script
-- Find out department size and their manager name
d0 = LOAD 'employees.departments' USING org.apache.hive.hcatalog.pig.HCatLoader();
dm0 = LOAD 'employees.dept_manager' USING org.apache.hive.hcatalog.pig.HCatLoader();
de0 = LOAD 'employees.dept_emp' USING org.apache.hive.hcatalog.pig.HCatLoader();
e0 = LOAD 'employees.employees' USING org.apache.hive.hcatalog.pig.HCatLoader();

d1 = FOREACH d0 GENERATE dept_no, dept_name;
dm1 = FOREACH (FILTER dm0 BY to_date >'2018-01-01') GENERATE dept_no, emp_no;
de1 = FOREACH (FILTER de0 by to_date >'2018-01-01') GENERATE dept_no, emp_no;
e1 = FOREACH e0 GENERATE emp_no, CONCAT(first_name,' ',last_name) AS fullname;

d1_dm1 = JOIN d1 BY dept_no, dm1 BY dept_no;
d1_dm1_e1 = JOIN d1_dm1 BY emp_no, e1 BY emp_no;

dept_mgr = FOREACH d1_dm1_e1 GENERATE d1_dm1::d1::dept_no AS dept_no, d1_dm1::d1::dept_name AS dept_name, e1::fullname AS manager;

d1_de1 = JOIN d1 BY dept_no, de1 BY dept_no;
d1_de1_e1 = FOREACH (JOIN d1_de1 BY emp_no, e1 BY emp_no) GENERATE d1_de1::d1::dept_no,e1::emp_no;
dept_emp_count = FOREACH (GROUP d1_de1_e1 BY dept_no) GENERATE group AS dept_no, COUNT(d1_de1_e1) AS employees;

dept_info_0 = JOIN dept_mgr BY dept_no, dept_emp_count BY dept_no;
dept_info_1 = FOREACH dept_info_0 GENERATE dept_mgr::dept_name AS dept_name, dept_mgr::manager AS manager, dept_emp_count::employees AS employees;
dept_info_2 = ORDER dept_info_1 BY dept_name;

DUMP dept_info_2;


[donghua@cdh-vm temp]$ date;pig -4 log4j.properties emp.pig;date;

Sun Feb 18 22:09:44 +08 2018

(Customer Service,Yuchang Weedman,17569)
(Development,Leon DasSarma,61386)
(Finance,Isamu Legleitner,12437)
(Human Resources,Karsten Sigstam,12898)
(Marketing,Vishwani Minakawa,14842)
(Production,Oscar Ghazalie,53304)
(Quality Management,Dung Pesch,14546)
(Research,Hilary Kambil,15441)
(Sales,Hauke Zhang,37701)

Sun Feb 18 22:16:08 +08 2018