[大数据]hive hqlsql 简单记录

hqlsql ： 官网地址为http://www.hplsql.org/home ，在高版本hive已经自带了，查看hive/bin/hplsql,内容如下

[root@140 bin]# cat hplsql
#!/usr/bin/env bash

# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

bin=`dirname "$0"`
bin=`cd "$bin"; pwd`

. "$bin"/hive --service hplsql "$@"

用途：Procedural SQL on Hadoop, NoSQL and RDBMS，支持跨数据库（未测试）进行数据。

主要测试了hive的存储过程，用于满足按日期循环汇总源数据到指定表的需求。

运行hplsql需要先配置hplsql-site.xml

[root@140 conf]# cat hplsql-site.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?> 
<configuration>

<property>
  <name>hplsql.conn.default</name>
  <value>hive2conn</value>
</property>

<property>
  <name>hplsql.onerror</name>
  <value>seterror</value>
</property>


<property>
  <name>hplsql.conn.hive2conn</name>
  <value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://hive-server:10000/default;root;root</value>
</property>
<property>
  <name>hplsql.conn.init.hive2conn</name>
  <value>
<!--     set mapred.job.queue.name=dev;-->
     set hive.execution.engine=mr;
     set hive.exec.reducers.max=1;
     set hive.auto.convert.join=false;
     use default;
  </value>
</property>
<property>
  <name>hplsql.conn.convert.hive2conn</name>
  <value>true</value>
</property>

<!-- <property>
  <name>hplsql.conn.hiveconn</name>
  <value>org.apache.hive.jdbc.HiveDriver;jdbc:hive2://</value>
</property>
-->

<property>
  <name>hplsql.conn.mysqlconn</name>
  <value>com.mysql.jdbc.Driver;jdbc:mysql://mysql:3306/ord;root;my-secret-pw</value>
  <description>MySQL connection</description>
</property>

</configuration>

注意上面的hplsql.conn.init.hive2conn，用于执行每个存储前需要初始化（提前执行）的命令。

以下是根据当前日期循环生成sql并且insert overwrite的例子：

-- test sql 
SET hplsql.onerror = seterror;

 
BEGIN
 
     day := TO_CHAR(CURRENT_DATE);

     PRINT 'day =' || day;

     SET  intday := CAST(SUBSTRING(TO_CHAR(CURRENT_DATE),9) AS INT8); 
     PRINT 'day =' || intday;
     inst_sql :='insert overwrite table order_test_all select *  from ('
     
     intday := intday - 1;

     PRINT 'loop days =' || intday;
  WHILE intday >0 LOOP
     table_name := 'order_test_' || REPLACE( TO_CHAR(CURRENT_DATE - intday  ),'-', '');
     inst_sql := inst_sql || '  select * from '|| table_name || ' UNION ALL ' ;
     
     intday := intday - 1;
 END LOOP;
   table_name := 'order_test_' || REPLACE( TO_CHAR(CURRENT_DATE ),'-', '');
 inst_sql := inst_sql || ' select * from '|| table_name || ') a ';
 
  PRINT 'SQL :=' || inst_sql;
  EXECUTE IMMEDIATE inst_sql;
  

END;

注意“-- test sql ”为注释。