集成Hive

SQLClient

SQL Client提供一种简单的方式来编写、调试和提交表程序（DDL、DQL、DML语句）到Flink 集群上，而无需写一行Scala 代码；能够在命令行中检索和可视化分布式应用中实时产生的结果。

SQL Client内嵌在常规 Flink 发行版中，因此可以直接运行，仅需要一个正在运行的 Flink 集群就可以在其中执行表程序。

• 启动Flink集群

xxxxxxxxxx
[root@node1 ~]# /opt/flink-salone/bin/start-cluster.sh 
Starting cluster.
Starting standalonesession daemon on host node1.
Starting taskexecutor daemon on host node1.
Starting taskexecutor daemon on host node2.
Starting taskexecutor daemon on host node3.

• 启动SQL Client

xxxxxxxxxx
[root@node1 ~]# /opt/flink-salone/bin/sql-client.sh

• 查看命令帮助

Flink SQL> HELP; The following commands are available:

HELP Prints the available commands. QUIT/EXIT Quits the SQL CLI client. CLEAR Clears the current terminal. SET Sets a session configuration property. Syntax: "SET ''='';". Use "SET;" for listing all properties. RESET Resets a session configuration property. Syntax: "RESET '';". Use "RESET;" for reset all session properties. INSERT INTO Inserts the results of a SQL SELECT query into a declared table sink. INSERT OVERWRITE Inserts the results of a SQL SELECT query into a declared table sink and overwrite existing data. SELECT Executes a SQL SELECT query on the Flink cluster. EXPLAIN Describes the execution plan of a query or table with the given name. ......

• 执行SQL语句

xxxxxxxxxx
Flink SQL> select 'GtJin' as src,lower('GtJin') as lower_type,upper('GtJin') as upper_type;

可视化的三种模式，通过命令：SET sql-client.execution.result-mode=xxx进行切换：

1. table：表格模式，也是默认模式

   xxxxxxxxxx
   Flink SQL>SET sql-client.execution.result-mode=table;
   

2. tableau：数据库表格模式

   xxxxxxxxxx
   Flink SQL>SET sql-client.execution.result-mode=tableau;
   Flink SQL>select 'GtJin' as src,lower('GtJin') as lower_type,upper('GtJin') as upper_type;
   

   

3. changelog：变更日志模式

   xxxxxxxxxx
   Flink SQL>SET sql-client.execution.result-mode=changelog;
   Flink SQL>select 'GtJin' as src,lower('GtJin') as lower_type,upper('GtJin') as upper_type;
   

   

案例：使用SQLClient完成指定单词的词频统计

xxxxxxxxxx
SET 'sql-client.execution.result-mode' = 'tableau';
SET 'execution.runtime-mode' = 'batch';
SET sql-client.execution.max-table-result.rows=2;
SELECT name,COUNT(*) AS cnt
FROM
  (VALUES ('Bob'), ('Alice'), ('Greg'), ('Bob')) AS NameTable(name)
GROUP BY name;