Tuesday, February 20, 2018

Apache Reverse Proxy Example for Cloudera Yarn

[root@cdh-vm conf.d]# cat /etc/httpd/conf.d/reverse.conf 
# Yarn Resource Manager
Listen 192.168.31.14:8088

    ProxyPreserveHost On
    ProxyPass / http://cdh-vm.dbaglobe.com:8088/
    ProxyPassReverse / http://cdh-vm.dbaglobe.com:8088/


# Yarn Node Manager
Listen 192.168.31.14:8042

    ProxyPreserveHost On
    ProxyPass / http://cdh-vm.dbaglobe.com:8042/
    ProxyPassReverse / http://cdh-vm.dbaglobe.com:8042/


# Yarn JobHistory Server
Listen 192.168.31.14:19888

    ProxyPreserveHost On
    ProxyPass / http://cdh-vm.dbaglobe.com:19888/
    ProxyPassReverse / http://cdh-vm.dbaglobe.com:19888/



Sunday, February 18, 2018

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

Saturday, February 17, 2018

Learning Apache Pig Chap 2 (Oreilly)

Sample data files:
https://resources.oreilly.com/examples/0636920047704/blob/master/Learning%20Apache%20Pig%20-%20Working%20Files/Chapter%202/cities_small.txt
https://resources.oreilly.com/examples/0636920047704/blob/master/Learning%20Apache%20Pig%20-%20Working%20Files/Chapter%202/states.txt



[donghua@cdh-vm temp]$ pig -4 log4j.properties 
grunt> cities = load 'cities_small.txt' as (name:chararray,state:chararray,pop:int);
grunt> aliases;
grunt> describe cities
cities: {name: chararray,state: chararray,pop: int}
grunt> \de cities
cities: {name: chararray,state: chararray,pop: int}
grunt> ca_cities = filter cities by (state=='CA');
grunt> dump ca_cities;
grunt> \d ca_cities
grunt> illustrate;
(South Gate,CA,96640)
--------------------------------------------------------------------
| cities     | name:chararray    | state:chararray    | pop:int    | 
--------------------------------------------------------------------
|            | South Gate        | CA                 | 96640      | 
--------------------------------------------------------------------

grunt> illustrate;
(Fresno,CA,476050)
--------------------------------------------------------------------
| cities     | name:chararray    | state:chararray    | pop:int    | 
--------------------------------------------------------------------
|            | Fresno            | CA                 | 476050     | 
--------------------------------------------------------------------

grunt> ordered_cities = order cities by pop desc;

grunt> states = load 'states.txt' as (rank:int,code:chararray,fullname:chararray,date_entered:chararray,year_entered:int);

grunt> cities_join_states = join cities by state, states by code;

grunt> illustrate cities_join_states;
(Fargo,ND,93531)
(39,ND,North Dakota,02-NOV,1889)
--------------------------------------------------------------------
| cities     | name:chararray    | state:chararray    | pop:int    | 
--------------------------------------------------------------------
|            | Fargo             | ND                 | 93531      | 
|            | Fargo             | ND                 | 93531      | 
--------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------
| states     | rank:int    | code:chararray    | fullname:chararray    | date_entered:chararray    | year_entered:int    | 
--------------------------------------------------------------------------------------------------------------------------
|            | 39          | ND                | North Dakota          | 02-NOV                    | 1889                | 
|            | 39          | ND                | North Dakota          | 02-NOV                    | 1889                | 
--------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| cities_join_states     | cities::name:chararray    | cities::state:chararray    | cities::pop:int    | states::rank:int    | states::code:chararray    | states::fullname:chararray    | states::date_entered:chararray    | states::year_entered:int    | 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                        | Fargo                     | ND                         | 93531              | 39                  | ND                        | North Dakota                  | 02-NOV                            | 1889                        | 
|                        | Fargo                     | ND                         | 93531              | 39                  | ND                        | North Dakota                  | 02-NOV                            | 1889                        | 
|                        | Fargo                     | ND                         | 93531              | 39                  | ND                        | North Dakota                  | 02-NOV                            | 1889                        | 
|                        | Fargo                     | ND                         | 93531              | 39                  | ND                        | North Dakota                  | 02-NOV                            | 1889                        | 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

grunt> cities_join_states_short = foreach cities_join_states generate cities::name, states::fullname;

grunt> store cities_join_states_short into 'cities_join_states_short';
grunt> fs -ls cities_join_states_short
grunt> fs -cat cities_join_states_short/part-r-00000



grunt> cities_join_states_short = foreach (join cities by state, states by code) generate cities::name, states::fullname;
grunt> city_and_state = foreach cities generate name,state,pop*1.5;

grunt> cities_by_state = group cities by state;
grunt> \de cities_by_state;
cities_by_state: {group: chararray,cities: {(name: chararray,state: chararray,pop: int)}}
grunt> illustrate cities_by_state;
(Sioux Falls,SD,154997)
-----------------------------------------------------------------------
| cities     | name:chararray     | state:chararray     | pop:int     | 
-----------------------------------------------------------------------
|            | Sioux Falls        | SD                  | 154997      | 
|            | Rapid City         | SD                  | 65491       | 
-----------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
| cities_by_state     | group:chararray     | cities:bag{:tuple(name:chararray,state:chararray,pop:int)}                     | 
------------------------------------------------------------------------------------------------------------------------------
|                     | SD                  | {(Sioux Falls, SD, 154997), (Rapid City, SD, 65491)}                           | 
------------------------------------------------------------------------------------------------------------------------------

grunt> total_cities = foreach (group cities all) generate COUNT(cities);
grunt> \d total_cities;
(500)

grunt> cities_by_state = foreach (group cities by state) generate group, COUNT(cities);
grunt> \d cities_by_state;

grunt> cities_by_state = foreach (group cities by state parallel 3) generate group, COUNT(cities);
grunt> store cities_by_state into 'cities_by_state';
grunt> fs -ls cities_by_state
Found 4 items
-rw-r--r--   1 donghua supergroup          0 2018-02-17 22:25 cities_by_state/_SUCCESS
-rw-r--r--   1 donghua supergroup        113 2018-02-17 22:25 cities_by_state/part-r-00000
-rw-r--r--   1 donghua supergroup         82 2018-02-17 22:25 cities_by_state/part-r-00001
-rw-r--r--   1 donghua supergroup         86 2018-02-17 22:25 cities_by_state/part-r-00002


Saturday, February 10, 2018

pycurl: libcurl link-time ssl backend (nss) is different from compile-time ssl backend (none/other)

[root@cdh-vm logs]# yum update -y
There was a problem importing one of the Python modules
required to run yum. The error leading to this problem was:

   pycurl: libcurl link-time ssl backend (nss) is different from compile-time ssl backend (none/other)

Please install a package which provides this module, or
verify that the module is installed correctly.

It's possible that the above module doesn't match the
current version of Python, which is:
2.7.5 (default, Aug  4 2017, 00:39:18) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-16)]

If you cannot solve this problem yourself, please go to 
the yum faq at:
  http://yum.baseurl.org/wiki/Faq
  
How to fix:

remove existing pycurl installation

[root@cdh-vm logs]# pip uninstall pycurl
export variable with your link-time ssl backend (which is nss above)

[root@cdh-vm logs]# export PYCURL_SSL_LIBRARY=nss
[root@cdh-vm logs]# pip install pycurl

How to upgrade outdated Python packages

[root@cdh-vm logs]# pip list --outdated --format=legacy 
argcomplete (1.8.2) - Latest: 1.9.3 [wheel]
backports.ssl-match-hostname (3.4.0.2) - Latest: 3.5.0.1 [sdist]
beautifulsoup4 (4.5.3) - Latest: 4.6.0 [wheel]
chardet (2.3.0) - Latest: 3.0.4 [wheel]
configobj (4.7.2) - Latest: 5.0.6 [sdist]
decorator (3.4.0) - Latest: 4.2.1 [wheel]
docx2txt (0.6) - Latest: 0.7 [sdist]
EbookLib (0.15) - Latest: 0.16 [sdist]
perf (0.1) - Latest: 1.5.1 [wheel]
psycopg2 (2.5.1) - Latest: 2.7.4 [wheel]
pycurl (7.19.0) - Latest: 7.43.0.1 [sdist]
pygobject (3.22.0) - Latest: 3.27.2 [sdist]
python-pptx (0.6.5) - Latest: 0.6.7 [sdist]
pyudev (0.15) - Latest: 0.21.0 [sdist]
pyxattr (0.5.1) - Latest: 0.6.0 [sdist]
setuptools (0.9.8) - Latest: 38.5.1 [wheel]
six (1.10.0) - Latest: 1.11.0 [wheel]
SpeechRecognition (3.6.3) - Latest: 3.8.1 [wheel]
urlgrabber (3.10) - Latest: 3.10.2 [sdist]
xlrd (1.0.0) - Latest: 1.1.0 [wheel]

[root@cdh-vm logs]# pip list --outdated --format=columns 
Package                      Version Latest   Type 
---------------------------- ------- -------- -----
argcomplete                  1.8.2   1.9.3    wheel
backports.ssl-match-hostname 3.4.0.2 3.5.0.1  sdist
beautifulsoup4               4.5.3   4.6.0    wheel
chardet                      2.3.0   3.0.4    wheel
configobj                    4.7.2   5.0.6    sdist
decorator                    3.4.0   4.2.1    wheel
docx2txt                     0.6     0.7      sdist
EbookLib                     0.15    0.16     sdist
perf                         0.1     1.5.1    wheel
psycopg2                     2.5.1   2.7.4    wheel
pycurl                       7.19.0  7.43.0.1 sdist
pygobject                    3.22.0  3.27.2   sdist
python-pptx                  0.6.5   0.6.7    sdist
pyudev                       0.15    0.21.0   sdist
pyxattr                      0.5.1   0.6.0    sdist
setuptools                   0.9.8   38.5.1   wheel
six                          1.10.0  1.11.0   wheel
SpeechRecognition            3.6.3   3.8.1    wheel
urlgrabber                   3.10    3.10.2   sdist
xlrd                         1.0.0   1.1.0    wheel

# Upgrade manually one by one
[root@cdh-vm logs]# pip install pycurl -U

# Upgrade all at once (high chance rollback if some package fail
# to upgrade
[root@cdh-vm logs]# pip install $(pip list --outdated --format=columns |tail -n +3|cut -d" " -f1) --upgrade

# Upgrade one by one using loop
[root@cdh-vm logs]# for i in  $(pip list --outdated --format=columns |tail -n +3|cut -d" " -f1); do pip install $i --upgrade; done

SQL Server JDBC integrated authentication error

Connection failure. You must change the Database Settings.
  com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:b31236b3-c830-45c9-bdb0-8e9ecbe01476
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2400)
      at com.microsoft.sqlserver.jdbc.AuthenticationJNI.(AuthenticationJNI.java:68)
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3132)
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:43)
      at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3123)
      at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1981)
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1628)
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1459)
      at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773)
      at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)
      at org.talend.core.model.metadata.builder.database.DriverShim.connect(DriverShim.java:41)
      at org.talend.core.model.metadata.builder.database.ExtractMetaDataUtils.connect(ExtractMetaDataUtils.java:1069)
      at org.talend.core.model.metadata.builder.database.ExtractMetaDataFromDataBase.testConnection(ExtractMetaDataFromDataBase.java:315)
      at org.talend.metadata.managment.repository.ManagerConnection.check(ManagerConnection.java:289)
      at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$62.runWithCancel(DatabaseForm.java:3983)
      at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$62.runWithCancel(DatabaseForm.java:1)
      at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$1.runnableWithCancel(AProgressMonitorDialogWithCancel.java:77)
      at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$ARunnableWithProgressCancel$1.call(AProgressMonitorDialogWithCancel.java:161)
      at java.util.concurrent.FutureTask.run(Unknown Source)
      at java.lang.Thread.run(Unknown Source)
  Caused by: java.lang.UnsatisfiedLinkError: no sqljdbc_auth in java.library.path
      at java.lang.ClassLoader.loadLibrary(Unknown Source)
      at java.lang.Runtime.loadLibrary0(Unknown Source)
      at java.lang.System.loadLibrary(Unknown Source)
      at com.microsoft.sqlserver.jdbc.AuthenticationJNI.(AuthenticationJNI.java:41)
      ... 20 more


How to fix: 

Copy OS architecture matching file (x32 or x64) into c:\Windows or C:\Windows\system32

PS C:\Users\Administrator> copy C:\Donghua\TOD-DI\sqljdbc_6.0\enu\auth\x64\sqljdbc_auth.dll C:\Windows\
PS C:\Users\Administrator> dir C:\Windows\sqljdbc_auth.dll


    Directory: C:\Windows


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----        1/17/2017  11:44 AM         310088 sqljdbc_auth.dll

Tuesday, February 6, 2018

warning: "set mapreduce.framework.name=local" terminates hiveserver2 server process

0: jdbc:hive2://cdh-vm:10000/employees> set mapreduce.framework.name=local;
No rows affected (0.015 seconds)
0: jdbc:hive2://cdh-vm:10000/employees> select id,count(*) num from t1 group by id order by num;
Unknown HS2 problem when communicating with Thrift server.
Error: org.apache.thrift.transport.TTransportException: java.net.SocketException: Broken pipe (Write failed) (state=08S01,code=0)

# /var/run/cloudera-scm-agent/process/175-hive-HIVESERVER2/logs/stderr.log
Job running in-process (local Hadoop)
+ ps -p 898 -c
+ grep java
+ RET=0
+ '[' 0 -eq 0 ']'
+ TARGET=898
++ date
+ echo Tue Feb 6 22:19:34 +08 2018
+ kill -9 898