Flink sql

1)  启动flink (1.10 , 1.13.1版本)

 ./bin/start-cluster.sh

[dq@mvxl11 flink-1.13.1]$
[dq@mvxl11 flink-1.13.1]$ ./bin/start-cluster.sh
Starting cluster.
Starting standalonesession daemon on host mvxl11.
Starting taskexecutor daemon on host mvxl111.
[dq@mvxl111 flink-1.13.1]$
[dq@mvxl11 flink-1.13.1]$ cd bin

[dq@mvxl111 bin]$ ll
total 2088
-rw-r--r-- 1 test test 2010313 May 25 20:16 bash-java-utils.jar
-rwxr-xr-x 1 test test   20562 May 21 17:26 config.sh
-rwxr-xr-x 1 test test    1318 Jan 30  2021 find-flink-home.sh
-rwxr-xr-x 1 test test    2381 May 21 17:26 flink
-rwxr-xr-x 1 test test    4137 May 21 17:26 flink-console.sh
-rwxr-xr-x 1 test test    6571 May 21 17:26 flink-daemon.sh
-rwxr-xr-x 1 test test    1564 Oct 29  2019 historyserver.sh
-rwxr-xr-x 1 test test    2295 Apr 19 22:33 jobmanager.sh
-rwxr-xr-x 1 test test    1650 May 21 17:26 kubernetes-jobmanager.sh
-rwxr-xr-x 1 test test    1717 May 21 17:26 kubernetes-session.sh
-rwxr-xr-x 1 test test    1770 May 21 17:26 kubernetes-taskmanager.sh
-rwxr-xr-x 1 test test    1133 Jan 30  2021 mesos-appmaster-job.sh
-rwxr-xr-x 1 test test    1137 Jan 30  2021 mesos-appmaster.sh
-rwxr-xr-x 1 test test    1958 May 21 17:26 mesos-jobmanager.sh
-rwxr-xr-x 1 test test    1891 May 21 17:26 mesos-taskmanager.sh
-rwxr-xr-x 1 test test    2994 May 21 17:26 pyflink-shell.sh
-rwxr-xr-x 1 test test    3742 May 21 17:26 sql-client.sh
-rwxr-xr-x 1 test test    2006 Apr 19 22:33 standalone-job.sh
-rwxr-xr-x 1 test test    1837 Jan 30  2021 start-cluster.sh
-rwxr-xr-x 1 test test    1854 Apr  7  2019 start-zookeeper-quorum.sh
-rwxr-xr-x 1 test test    1617 Jan 30  2021 stop-cluster.sh
-rwxr-xr-x 1 test test    1845 Apr  7  2019 stop-zookeeper-quorum.sh
-rwxr-xr-x 1 test test    2960 May 21 17:26 taskmanager.sh
-rwxr-xr-x 1 test test    1725 May 21 17:26 yarn-session.sh
-rwxr-xr-x 1 test test    2405 Apr 13 20:12 zookeeper.sh
[test@mvxl11 bin]$
[test@mvxl11 bin]$
[test@mvxl11 bin]$ ./bin/sql-client.sh embedded
-bash: ./bin/sql-client.sh: No such file or directory
[test@mvxl11 bin]$ cd ..
[test@mvxl11 flink-1.13.1]$ ./bin/sql-client.sh embedded
No default environment specified.
Searching for '/data/test/flinkTest/flink-1.13.1/conf/sql-client-defaults.yaml'...not found.
Command history file path: /home/test/.flink-sql-history

                                   ▒▓██▓██▒
                               ▓████▒▒█▓▒▓███▓▒
                            ▓███▓░░        ▒▒▒▓██▒  ▒
                          ░██▒   ▒▒▓▓█▓▓▒░      ▒████
                          ██▒         ░▒▓███▒    ▒█▒█▒
                            ░▓█            ███   ▓░▒██
                              ▓█       ▒▒▒▒▒▓██▓░▒░▓▓█
                            █░ █   ▒▒░       ███▓▓█ ▒█▒▒▒
                            ████░   ▒▓█▓      ██▒▒▒ ▓███▒
                         ░▒█▓▓██       ▓█▒    ▓█▒▓██▓ ░█░
                   ▓░▒▓████▒ ██         ▒█    █▓░▒█▒░▒█▒
                  ███▓░██▓  ▓█           █   █▓ ▒▓█▓▓█▒
                ░██▓  ░█░            █  █▒ ▒█████▓▒ ██▓░▒
               ███░ ░ █░          ▓ ░█ █████▒░░    ░█░▓  ▓░
              ██▓█ ▒▒▓▒          ▓███████▓░       ▒█▒ ▒▓ ▓██▓
           ▒██▓ ▓█ █▓█       ░▒█████▓▓▒░         ██▒▒  █ ▒  ▓█▒
           ▓█▓  ▓█ ██▓ ░▓▓▓▓▓▓▓▒              ▒██▓           ░█▒
           ▓█    █ ▓███▓▒░              ░▓▓▓███▓          ░▒░ ▓█
           ██▓    ██▒    ░▒▓▓███▓▓▓▓▓██████▓▒            ▓███  █
          ▓███▒ ███   ░▓▓▒░░   ░▓████▓░                  ░▒▓▒  █▓
          █▓▒▒▓▓██  ░▒▒░░░▒▒▒▒▓██▓░                            █▓
          ██ ▓░▒█   ▓▓▓▓▒░░  ▒█▓       ▒▓▓██▓    ▓▒          ▒▒▓
          ▓█▓ ▓▒█  █▓░  ░▒▓▓██▒            ░▓█▒   ▒▒▒░▒▒▓█████▒
           ██░ ▓█▒█▒  ▒▓▓▒  ▓█                █░      ░░░░   ░█▒
           ▓█   ▒█▓   ░     █░                ▒█              █▓
            █▓   ██         █░                 ▓▓        ▒█▓▓▓▒█░
             █▓ ░▓██░       ▓▒                  ▓█▓▒░░░▒▓█░    ▒█
              ██   ▓█▓░      ▒                    ░▒█▒██▒      ▓▓
               ▓█▒   ▒█▓▒░                         ▒▒ █▒█▓▒▒░░▒██
                ░██▒    ▒▓▓▒                     ▓██▓▒█▒ ░▓▓▓▓▒█▓
                  ░▓██▒                          ▓░  ▒█▓█  ░░▒▒▒
                      ▒▓▓▓▓▓▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒░░▓▓  ▓░▒█░

    ______ _ _       _       _____  ____  _         _____ _ _            _  BETA
   |  ____| (_)     | |     / ____|/ __ \| |       / ____| (_)          | |
   | |__  | |_ _ __ | | __ | (___ | |  | | |      | |    | |_  ___ _ __ | |_
   |  __| | | | '_ \| |/ /  \___ \| |  | | |      | |    | | |/ _ \ '_ \| __|
   | |    | | | | | |   <   ____) | |__| | |____  | |____| | |  __/ | | | |_
   |_|    |_|_|_| |_|_|\_\ |_____/ \___\_\______|  \_____|_|_|\___|_| |_|\__|

        Welcome! Enter 'HELP;' to list all available commands. 'QUIT;' to exit.


Flink SQL>
>
> SELECT name, COUNT(*) AS cnt FROM (VALUES ('Bob'), ('Alice'), ('Greg'), ('Bob')) AS nameTable(name) GROUP BY name;
[INFO] Result retrieval cancelled.

Flink SQL>

2 基本用法

Flink SQL> SELECT name, COUNT(*) AS cnt 
FROM (VALUES ('Bob'), ('Alice'), ('Greg'), ('Bob')) AS nameTable(name) 
GROUP BY name;

3 连接kafka

3.1 增加扩展包(默认的只支持csv,file等文件系统)

flink-json-1.10.2.jar   （https://repo1.maven.org/maven2/org/apache/flink/flink-json/1.10.2/flink-json-1.10.2.jar）

flink-sql-connector-kafka_2.11-1.10.2.jar （https://repo1.maven.org/maven2/org/apache/flink/flink-sql-connector-kafka_2.11/1.10.2/flink-sql-connector-kafka_2.11-1.10.2.jar）

将jar包flink 文件夹下的lib文件夹下

3.2 启动sql-client

./bin/sql-client.sh embedded -l lib/

 -l,--library <JAR directory>  可以参考 Apache Flink 1.11 Documentation: SQL 客户端

 -l,--library <JAR directory>  可以参考 Apache Flink 1.11 Documentation: SQL 客户端

3.3 创建 kafka Table

CREATE TABLE CustomerStatusChangedEvent(customerId int,
oStatus int,
nStatus int)with('connector.type' = 'kafka',
'connector.version' = 'universal',
'connector.properties.group.id' = 'g2.group1',
'connector.properties.bootstrap.servers' = '192.168.1.85:9092,192.168.1.86:9092',
'connector.properties.zookeeper.connect' = '192.168.1.85:2181',
'connector.topic' = 'customer_statusChangedEvent',
'connector.startup-mode' = 'earliest-offset',
'format.type' = 'json');

3.4 插入数据

insert into CustomerStatusChangedEvent(customerId,oStatus,nStatus)
values(1001,1,2),(1002,10,2),(1003,1,20);

3.4 执行查询

select *  from CustomerStatusChangedEvent ;