CC00063.flink——|Hadoop&Flink.V02|——|Flink.v02|Flink SQL|Flink SQL外部链接|从文件获取数据流|

一、外部链接：Connectors

Name	VersIon	Maven dependency	SQL CIIentJAR
Filesystem		Built-in	Built-in
Elasticsearch	6	flink-connector-elasticsearch6	Download
Elasticsearch	7	flink-connector-elasticsearch7	Download
Apache Kafka	0.10	flink-connector-kafka-0.10	Download
Apache Kafka	0.11	flink-connector-kafka-0.11	Download
Apache Kafka	0.11+( universal )	flink-connector-kafka	Download
Apache HBase	1.4.3	flink-connector-hbase	Download
JDBC		flink-connector-jdbc	Download

二、Formats

Name	Maven dependency	SQL Cllent JAR
Old CSV (for files)	Built-in	Built-in
CSV (for Kafka)	flink-csv	Built-in
JSON	flink-json	Built-in
Apache Avro	flink-avro	Download

三、外部链接编程实现流程

### --- 数据查询语言DQL

~~~     # 数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
~~~     子句组成的查询块：
~~~     SELECT <字段名表>
~~~     FROM <表或视图名>
~~~     WHERE <查询条件>

### --- 数据操纵语言DML

~~~     # 数据操纵语言DML主要有三种形式：
~~~     插入：INSERT
~~~     更新：UPDATE
~~~     删除：DELETE

### --- 数据定义语言DDL

~~~     # 数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：
~~~     CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
~~~     表 视图 索引 同义词 簇
~~~     DDL操作是隐性提交的！不能rollback

### --- 数据控制语言DCL

~~~     数据控制语言DCL用来授予或回收访问数据库的某种特权，
~~~     并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。
~~~     如：连接外部系统在 Catalog 中注册表，直接调用 tableEnv.connect()就可以，
~~~     里面参数要传入一个 ConnectorDescriptor，也就是 connector 描述器。
~~~     对于文件系统的 connector 而言，flink 内部已经提供了，就叫做 FileSystem()。

<dependency>
            <groupId>org.apache.flink</groupId>
            <artifactId>flink-csv</artifactId>
            <version>1.11.1</version>
        </dependency>

tEnv.connect(new FileSystem().path("sensor.txt"))       # 定义表数据来源，外部连接
        .withFormat(new Csv())                          # 定义从外部系统读取数据之后的格式化方法
        .withSchema(new Schema()
        .field("id", DataTypes.STRING())
        .field("timestamp", DataTypes.BIGINT())
        .field("temperature", DataTypes.DOUBLE()))      # 定义表结构
        .createTemporaryTable("inputTable");            # 创建临时表

### --- 连接Kafka：

    ConnectTableDescriptor descriptor = tEnv.connect(
        // declare the external system to connect to
            new Kafka()
                    .version("universal")
                    .topic("animal")
                    .startFromEarliest()
                    .property("bootstrap.servers", "hdp-2:9092")
    )
            // declare a format for this system
            .withFormat(
                    // new Json()
                    new Csv()
            )
            // declare the schema of the table
            .withSchema(
                    new Schema()
        // .field("rowtime", DataTypes.TIMESTAMP(3))
        // .rowtime(new Rowtime()
        // .timestampsFromField("timestamp")
        // .watermarksPeriodicBounded(60000)
        // )
        // .field("user", DataTypes.BIGINT())
        .field("message", DataTypes.STRING())
    );

// create a table with given name
descriptor.createTemporaryTable("MyUserTable");
        Table table1 = tEnv.sqlQuery("select * from MyUserTable");
        DataStream<Tuple2<Boolean, Row>> tuple2DataStream =
        tEnv.toRetractStream(table1, Row.class);
        tuple2DataStream.print();

四、编程代码实现：从文件系统中获取数据

### --- 编程代码实现

package com.yanqi.tableql;

import org.apache.flink.api.java.tuple.Tuple2;
import org.apache.flink.streaming.api.datastream.DataStream;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.DataTypes;
import org.apache.flink.table.api.EnvironmentSettings;
import org.apache.flink.table.api.Table;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;
import org.apache.flink.table.descriptors.Csv;
import org.apache.flink.table.descriptors.FileSystem;
import org.apache.flink.table.descriptors.Schema;
import org.apache.flink.types.Row;

public class FromFileSystem {
    public static void main(String[] args) throws Exception {
        //env
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        //tEnv
        EnvironmentSettings settings = EnvironmentSettings.newInstance()
                .useBlinkPlanner()
                .inStreamingMode()
//                .inBatchMode()
                .withBuiltInCatalogName("default_catalog")
                .withBuiltInDatabaseName("default_database")
                .build();
        StreamTableEnvironment tEnv = StreamTableEnvironment.create(env, settings);

        //从文件系统中获取数据
        tEnv.connect(new FileSystem().path("e:\\data\\input\\hello.txt"))
                .withFormat(new Csv())
                .withSchema(
                        new Schema()
                        .field("name", DataTypes.STRING())
                )
                .createTemporaryTable("nameTable");

        String sql = "select * from nameTable";
        Table resultTable = tEnv.sqlQuery(sql);

        DataStream<Tuple2<Boolean, Row>> tuple2DataStream = tEnv.toRetractStream(resultTable, Row.class);

        tuple2DataStream.print();

        env.execute();


    }
}

### --- 编译打印

D:\JAVA\jdk1.8.0_231\bin\java.exe "-javaagent:D:\IntelliJIDEA\IntelliJ IDEA 2019.3.3\lib\idea_rt.jar=55725:D:\IntelliJIDEA\IntelliJ IDEA 2019.3.3\bin" -Dfile.encoding=UTF-8 -classpath D:\JAVA\jdk1.8.0_231\jre\lib\charsets.jar;D:\JAVA\jdk1.8.0_231\jre\lib\deploy.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\access-bridge-64.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\cldrdata.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\dnsns.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\jaccess.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\jfxrt.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\localedata.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\nashorn.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\sunec.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\sunjce_provider.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\sunmscapi.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\sunpkcs11.jar;D:\JAVA\jdk1.8.0_231\jre\lib\ext\zipfs.jar;D:\JAVA\jdk1.8.0_231\jre\lib\javaws.jar;D:\JAVA\jdk1.8.0_231\jre\lib\jce.jar;D:\JAVA\jdk1.8.0_231\jre\lib\jfr.jar;D:\JAVA\jdk1.8.0_231\jre\lib\jfxswt.jar;D:\JAVA\jdk1.8.0_231\jre\lib\jsse.jar;D:\JAVA\jdk1.8.0_231\jre\lib\management-agent.jar;D:\JAVA\jdk1.8.0_231\jre\lib\plugin.jar;D:\JAVA\jdk1.8.0_231\jre\lib\resources.jar;D:\JAVA\jdk1.8.0_231\jre\lib\rt.jar;E:\NO.Z.80000.Hadoop.spark\FirstFlink\target\classes;D:\JAVA\scala-2.12.2\lib\scala-library.jar;D:\JAVA\scala-2.12.2\lib\scala-reflect.jar;C:\Users\Administrator\.m2\repository\org\apache\flink\flink-java\1.11.1\flink-java-1.11.1.jar;C:\Users\Administrator\.m2\repository\org\apache\flink\flink-core\1.11.1\flink-core-1.11.1.jar;C:\Users\Administrator\.m2\repository\org\apache\flink\flink-annotations\1.11.1\flink-annotations-1.11.1.jar;C:\Users\Administrator\.m2\repository\org\apache\flink\flink-metrics-core\1.11.1\flink-metrics-core-1.11.1.jar;C:\Users\Administrator\.m2\repository\com\esotericsoftware\kryo\kryo\2.24.0\kryo-2.24.0.jar;C:\Users\Administrator\.m2\repository\com\esotericsoftware\minlog\minlog\1.2\minlog-1.2.jar;C:\Users\Administrator\.m2\repository\org\objenesis\objenesis\2.1\objenesis-2.1.jar;C:\Users\Administrator\.m2\repository\org\apache\commons\commons-lang3\3.3.2\commons-lang3-3.3.2.jar;C:\Users\Administrator\.m2\repository\org\apache\commons\commons-math3\3.5\commons-math3-3