Shulou(Shulou.com)06/01 Report--

This article mainly introduces "how to select data from MYSQL database tables". In daily operation, I believe that many people have doubts about how to select data from MYSQL database tables. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "how to select data from MYSQL database tables". Next, please follow the editor to study!

Use Tbale SQL and Flink JDBC connectors to select data from MYSQL database tables with SELECT

Sample environment

Java.version: 1.8.xflink.version: 1.11.1kafka:2.11

SelectToMysql.java

Package com.flink.examples.mysql;import org.apache.flink.api.common.functions.FlatMapFunction;import org.apache.flink.streaming.api.datastream.DataStream;import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;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.types.Row;import org.apache.flink.util.Collector Import static org.apache.flink.table.api.Expressions.$;/** * @ Description uses the Tbale&SQL and Flink JDBC connectors to select data from SELECT in the MYSQL database table. * / public class SelectToMysql {/ * * official reference: https://ci.apache.org/projects/flink/flink-docs-release-1.11/dev/table/connectors/jdbc.html Partition scanning to speed up data reading in parallel Source task instances, Flink provides partition scanning for JDBC tables. Scan.partition.column: the column name used to partition the input. Scan.partition.num: number of partitions. Scan.partition.lower-bound: the minimum value of the first partition. Scan.partition.upper-bound: the maximum of the last partition. * / flink-jdbc-1.11.1, all attribute names are defined in the JdbcTableSourceSinkFactory factory class static String table_sql = "CREATE TABLE my_users (\ n" + "id BIGINT,\ n" + "name STRING,\ n" + "age INT,\ n" + "status INT") \ n "+" PRIMARY KEY (id) NOT ENFORCED\ n "+") WITH (\ n "+" 'connector.type' =' jdbc',\ n "+" 'connector.url' =' jdbc:mysql://192.168.110.35:3306/flink?useUnicode=true&characterEncoding=utf-8',\ n "+" 'connector.driver' =' com.mysql.jdbc.Driver' \ n "+" 'connector.table' =' users',\ n "+" 'connector.username' =' root',\ n "+" 'connector.password' =' password',\ n "+" 'connector.read.fetch-size' =' 3'\ n "+") " Public static void main (String [] args) throws Exception {/ / build StreamExecutionEnvironment StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment (); / / build EnvironmentSettings and specify BlinkPlanner EnvironmentSettings bsSettings = EnvironmentSettings.newInstance (). UseBlinkPlanner (). InStreamingMode (). Build (); / / build StreamTableEnvironment StreamTableEnvironment tEnv = StreamTableEnvironment.create (env, bsSettings); / / register mysql data dimension table tEnv.executeSql (table_sql) String sql = "select id,name,age,status from my_users"; / / first: execute SQL / / Table table = tEnv.sqlQuery (sql) / / second: assemble the execution statement Table table = tEnv.from ("my_users"). Select ($("id"), $("name"), $("age"), $("status"). Where ($("status"). IsEqual (0)); / / print field structure table.printSchema () / / convert table to dataStream stream DataStream behaviorStream = tEnv.toAppendStream (table, Row.class); behaviorStream.flatMap (new FlatMapFunction () {@ Override public void flatMap (Row value, Collector out) throws Exception {System.out.println (value.toString ()); Thread.sleep (1 * 1000);}}) .print () Env.execute ();}}

Print the result

Root |-- id: BIGINT NOT NULL |-- name: STRING |-- age: INT |-- status: INT1,nike,16,02,nike,18,0 so far, the study on "how to select data from SELECT in MYSQL database tables" is over. I hope to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.