Java Code Examples for org.apache.flink.table.api.TableEnvironment#execute()

@Test
public void testDefaultPartitionName() throws Exception {
	hiveShell.execute("create database db1");
	hiveShell.execute("create table db1.src (x int, y int)");
	hiveShell.execute("create table db1.part (x int) partitioned by (y int)");
	hiveShell.insertInto("db1", "src").addRow(1, 1).addRow(2, null).commit();

	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();

	// test generating partitions with default name
	tableEnv.sqlUpdate("insert into db1.part select * from db1.src");
	tableEnv.execute("mytest");
	HiveConf hiveConf = hiveShell.getHiveConf();
	String defaultPartName = hiveConf.getVar(HiveConf.ConfVars.DEFAULTPARTITIONNAME);
	Table hiveTable = hmsClient.getTable("db1", "part");
	Path defaultPartPath = new Path(hiveTable.getSd().getLocation(), "y=" + defaultPartName);
	FileSystem fs = defaultPartPath.getFileSystem(hiveConf);
	assertTrue(fs.exists(defaultPartPath));

	// TODO: test reading from flink when https://issues.apache.org/jira/browse/FLINK-13279 is fixed
	assertEquals(Arrays.asList("1\t1", "2\tNULL"), hiveShell.executeQuery("select * from db1.part"));

	hiveShell.execute("drop database db1 cascade");
}
 

@Test
public void testDecimal() throws Exception {
	hiveShell.execute("create database db1");
	try {
		hiveShell.execute("create table db1.src1 (x decimal(10,2))");
		hiveShell.execute("create table db1.src2 (x decimal(10,2))");
		hiveShell.execute("create table db1.dest (x decimal(10,2))");
		// populate src1 from Hive
		hiveShell.execute("insert into db1.src1 values (1.0),(2.12),(5.123),(5.456),(123456789.12)");

		TableEnvironment tableEnv = getTableEnvWithHiveCatalog();
		// populate src2 with same data from Flink
		tableEnv.sqlUpdate("insert into db1.src2 values (cast(1.0 as decimal(10,2))), (cast(2.12 as decimal(10,2))), " +
				"(cast(5.123 as decimal(10,2))), (cast(5.456 as decimal(10,2))), (cast(123456789.12 as decimal(10,2)))");
		tableEnv.execute("test1");
		// verify src1 and src2 contain same data
		verifyHiveQueryResult("select * from db1.src2", hiveShell.executeQuery("select * from db1.src1"));

		// populate dest with src1 from Flink -- to test reading decimal type from Hive
		tableEnv.sqlUpdate("insert into db1.dest select * from db1.src1");
		tableEnv.execute("test2");
		verifyHiveQueryResult("select * from db1.dest", hiveShell.executeQuery("select * from db1.src1"));
	} finally {
		hiveShell.execute("drop database db1 cascade");
	}
}
 

@Test
public void testInsertIntoNonPartitionTable() throws Exception {
	String dbName = "default";
	String tblName = "dest";
	RowTypeInfo rowTypeInfo = createDestTable(dbName, tblName, 0);
	ObjectPath tablePath = new ObjectPath(dbName, tblName);

	TableEnvironment tableEnv = HiveTestUtils.createTableEnv();
	List<Row> toWrite = generateRecords(5);
	Table src = tableEnv.fromTableSource(new CollectionTableSource(toWrite, rowTypeInfo));
	tableEnv.registerTable("src", src);

	tableEnv.registerCatalog("hive", hiveCatalog);
	tableEnv.sqlQuery("select * from src").insertInto("hive", "default", "dest");
	tableEnv.execute("mytest");

	verifyWrittenData(toWrite, hiveShell.executeQuery("select * from " + tblName));

	hiveCatalog.dropTable(tablePath, false);
}
 

@Test
public void testInsertIntoDynamicPartition() throws Exception {
	String dbName = "default";
	String tblName = "dest";
	RowTypeInfo rowTypeInfo = createDestTable(dbName, tblName, 1);
	ObjectPath tablePath = new ObjectPath(dbName, tblName);

	TableEnvironment tableEnv = HiveTestUtils.createTableEnv();

	List<Row> toWrite = generateRecords(5);
	Table src = tableEnv.fromTableSource(new CollectionTableSource(toWrite, rowTypeInfo));
	tableEnv.registerTable("src", src);

	tableEnv.registerCatalog("hive", hiveCatalog);
	tableEnv.sqlQuery("select * from src").insertInto("hive", "default", "dest");
	tableEnv.execute("mytest");

	List<CatalogPartitionSpec> partitionSpecs = hiveCatalog.listPartitions(tablePath);
	assertEquals(toWrite.size(), partitionSpecs.size());

	verifyWrittenData(toWrite, hiveShell.executeQuery("select * from " + tblName));

	hiveCatalog.dropTable(tablePath, false);
}
 

public static void main(String[] args) throws Exception {
	ParameterTool params = ParameterTool.fromArgs(args);
	String outputPath = params.getRequired("outputPath");
	String sqlStatement = params.getRequired("sqlStatement");

	TableEnvironment tEnv = TableEnvironment.create(EnvironmentSettings.newInstance()
		.useBlinkPlanner()
		.inBatchMode()
		.build());

	tEnv.registerTableSource("table1", new GeneratorTableSource(10, 100, 60, 0));
	tEnv.registerTableSource("table2", new GeneratorTableSource(5, 0.2f, 60, 5));
	tEnv.registerTableSink("sinkTable",
		new CsvTableSink(outputPath)
			.configure(new String[]{"f0", "f1"}, new TypeInformation[]{Types.INT, Types.SQL_TIMESTAMP}));

	tEnv.sqlUpdate(sqlStatement);
	tEnv.execute("TestSqlJob");
}
 

@Test
public void testTableReadStartFromLatestByDefault() throws Exception {
    String pulsarCatalog1 = "pulsarcatalog1";

    String tableName = newTopic();

    sendTypedMessages(tableName, SchemaType.INT32, INTEGER_LIST, Optional.empty());

    ExecutionContext context = createExecutionContext(CATALOGS_ENVIRONMENT_FILE, getStreamingConfs());
    TableEnvironment tableEnv = context.createEnvironmentInstance().getTableEnvironment();

    tableEnv.useCatalog(pulsarCatalog1);

    Table t = tableEnv.scan(TopicName.get(tableName).getLocalName()).select("value");
    DataStream stream = ((StreamTableEnvironment) tableEnv).toAppendStream(t, t.getSchema().toRowType());
    stream.map(new FailingIdentityMapper<Row>(INTEGER_LIST.size()))
            .addSink(new SingletonStreamSink.StringSink<>()).setParallelism(1);

    Thread runner = new Thread("runner") {
        @Override
        public void run() {
            try {
                tableEnv.execute("read from latest");
            } catch (Throwable e) {
                // do nothing
            }
        }
    };

    runner.start();

    Thread.sleep(2000);
    sendTypedMessages(tableName, SchemaType.INT32, INTEGER_LIST, Optional.empty());

    Thread.sleep(2000);
    SingletonStreamSink.compareWithList(INTEGER_LIST.subList(0, INTEGER_LIST.size() - 1).stream().map(Objects::toString).collect(Collectors.toList()));
}
 

@Test
public void testTableReadStartFromEarliest() throws Exception {
    String tableName = newTopic();

    sendTypedMessages(tableName, SchemaType.INT32, INTEGER_LIST, Optional.empty());

    Map<String, String> conf = getStreamingConfs();
    conf.put("$VAR_STARTING", "earliest");

    ExecutionContext context = createExecutionContext(CATALOGS_ENVIRONMENT_FILE_START, conf);
    TableEnvironment tableEnv = context.createEnvironmentInstance().getTableEnvironment();

    tableEnv.useCatalog("pulsarcatalog1");

    Table t = tableEnv.scan(TopicName.get(tableName).getLocalName()).select("value");
    DataStream stream = ((StreamTableEnvironment) tableEnv).toAppendStream(t, t.getSchema().toRowType());
    stream.map(new FailingIdentityMapper<Row>(INTEGER_LIST.size()))
            .addSink(new SingletonStreamSink.StringSink<>()).setParallelism(1);

    Thread runner = new Thread("runner") {
        @Override
        public void run() {
            try {
                tableEnv.execute("read from earliest");
            } catch (Throwable e) {
                // do nothing
            }
        }
    };

    runner.start();

    Thread.sleep(2000);
    SingletonStreamSink.compareWithList(INTEGER_LIST.subList(0, INTEGER_LIST.size() - 1).stream().map(Objects::toString).collect(Collectors.toList()));
}
 

@Test
public void testGetNonExistingFunction() throws Exception {
	hiveShell.execute("create database db1");
	hiveShell.execute("create table db1.src (d double, s string)");
	hiveShell.execute("create table db1.dest (x bigint)");

	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();

	// just make sure the query runs through, no need to verify result
	tableEnv.sqlUpdate("insert into db1.dest select count(d) from db1.src");
	tableEnv.execute("test");

	hiveShell.execute("drop database db1 cascade");
}
 

private void readWriteFormat(String format) throws Exception {
	TableEnvironment tableEnv = getTableEnvWithHiveCatalog();

	hiveShell.execute("create database db1");

	// create source and dest tables
	String suffix;
	if (format.equals("csv")) {
		suffix = "row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'";
	} else {
		suffix = "stored as " + format;
	}
	hiveShell.execute("create table db1.src (i int,s string) " + suffix);
	hiveShell.execute("create table db1.dest (i int,s string) " + suffix);

	// prepare source data with Hive
	hiveShell.execute("insert into db1.src values (1,'a'),(2,'b')");

	// populate dest table with source table
	tableEnv.sqlUpdate("insert into db1.dest select * from db1.src");
	tableEnv.execute("test_" + format);

	// verify data on hive side
	verifyHiveQueryResult("select * from db1.dest", Arrays.asList("1\ta", "2\tb"));

	hiveShell.execute("drop database db1 cascade");
}
 

@Test
public void testWriteNestedComplexType() throws Exception {
	String dbName = "default";
	String tblName = "dest";
	ObjectPath tablePath = new ObjectPath(dbName, tblName);

	// nested complex types
	TableSchema.Builder builder = new TableSchema.Builder();
	// array of rows
	builder.fields(new String[]{"a"}, new DataType[]{DataTypes.ARRAY(
			DataTypes.ROW(DataTypes.FIELD("f1", DataTypes.INT()), DataTypes.FIELD("f2", DataTypes.STRING())))});
	RowTypeInfo rowTypeInfo = createDestTable(dbName, tblName, builder.build(), 0);
	Row row = new Row(rowTypeInfo.getArity());
	Object[] array = new Object[3];
	row.setField(0, array);
	for (int i = 0; i < array.length; i++) {
		Row struct = new Row(2);
		struct.setField(0, 1 + i);
		struct.setField(1, String.valueOf((char) ('a' + i)));
		array[i] = struct;
	}
	List<Row> toWrite = new ArrayList<>();
	toWrite.add(row);

	TableEnvironment tableEnv = HiveTestUtils.createTableEnv();

	Table src = tableEnv.fromTableSource(new CollectionTableSource(toWrite, rowTypeInfo));
	tableEnv.registerTable("nestedSrc", src);
	tableEnv.registerCatalog("hive", hiveCatalog);
	tableEnv.sqlQuery("select * from nestedSrc").insertInto("hive", "default", "dest");
	tableEnv.execute("mytest");

	List<String> result = hiveShell.executeQuery("select * from " + tblName);
	assertEquals(1, result.size());
	assertEquals("[{\"f1\":1,\"f2\":\"a\"},{\"f1\":2,\"f2\":\"b\"},{\"f1\":3,\"f2\":\"c\"}]", result.get(0));
	hiveCatalog.dropTable(tablePath, false);
}
 

@Test
public void testInsertIntoStaticPartition() throws Exception {
	String dbName = "default";
	String tblName = "dest";
	RowTypeInfo rowTypeInfo = createDestTable(dbName, tblName, 1);
	ObjectPath tablePath = new ObjectPath(dbName, tblName);

	TableEnvironment tableEnv = HiveTestUtils.createTableEnv();
	List<Row> toWrite = generateRecords(1);
	Table src = tableEnv.fromTableSource(new CollectionTableSource(toWrite, rowTypeInfo));
	tableEnv.registerTable("src", src);

	Map<String, String> partSpec = new HashMap<>();
	partSpec.put("s", "a");

	CatalogTable table = (CatalogTable) hiveCatalog.getTable(tablePath);
	HiveTableSink hiveTableSink = new HiveTableSink(new JobConf(hiveConf), tablePath, table);
	hiveTableSink.setStaticPartition(partSpec);
	tableEnv.registerTableSink("destSink", hiveTableSink);
	tableEnv.sqlQuery("select * from src").insertInto("destSink");
	tableEnv.execute("mytest");

	// make sure new partition is created
	assertEquals(toWrite.size(), hiveCatalog.listPartitions(tablePath).size());

	verifyWrittenData(toWrite, hiveShell.executeQuery("select * from " + tblName));

	hiveCatalog.dropTable(tablePath, false);
}
 

@Test
public void testInsertOverwrite() throws Exception {
	String dbName = "default";
	String tblName = "dest";
	RowTypeInfo rowTypeInfo = createDestTable(dbName, tblName, 0);
	ObjectPath tablePath = new ObjectPath(dbName, tblName);

	TableEnvironment tableEnv = HiveTestUtils.createTableEnv();

	// write some data and verify
	List<Row> toWrite = generateRecords(5);
	Table src = tableEnv.fromTableSource(new CollectionTableSource(toWrite, rowTypeInfo));
	tableEnv.registerTable("src", src);

	CatalogTable table = (CatalogTable) hiveCatalog.getTable(tablePath);
	tableEnv.registerTableSink("destSink", new HiveTableSink(new JobConf(hiveConf), tablePath, table));
	tableEnv.sqlQuery("select * from src").insertInto("destSink");
	tableEnv.execute("mytest");

	verifyWrittenData(toWrite, hiveShell.executeQuery("select * from " + tblName));

	// write some data to overwrite existing data and verify
	toWrite = generateRecords(3);
	Table src1 = tableEnv.fromTableSource(new CollectionTableSource(toWrite, rowTypeInfo));
	tableEnv.registerTable("src1", src1);

	HiveTableSink sink = new HiveTableSink(new JobConf(hiveConf), tablePath, table);
	sink.setOverwrite(true);
	tableEnv.registerTableSink("destSink1", sink);
	tableEnv.sqlQuery("select * from src1").insertInto("destSink1");
	tableEnv.execute("mytest");

	verifyWrittenData(toWrite, hiveShell.executeQuery("select * from " + tblName));

	hiveCatalog.dropTable(tablePath, false);
}
 

private void testUserDefinedCatalogFunction(TableEnvironment tableEnv, String createFunctionDDL) throws Exception {
	List<Row> sourceData = Arrays.asList(
		Row.of(1, "1000", 2),
		Row.of(2, "1", 3),
		Row.of(3, "2000", 4),
		Row.of(1, "2", 2),
		Row.of(2, "3000", 3)
	);

	TestCollectionTableFactory.reset();
	TestCollectionTableFactory.initData(sourceData, new ArrayList<>(), -1);

	String sourceDDL = "create table t1(a int, b varchar, c int) with ('connector' = 'COLLECTION')";
	String sinkDDL = "create table t2(a int, b varchar, c int) with ('connector' = 'COLLECTION')";
	String query = " insert into t2 select t1.a, t1.b, addOne(t1.a, 1) as c from t1";

	tableEnv.sqlUpdate(sourceDDL);
	tableEnv.sqlUpdate(sinkDDL);
	tableEnv.sqlUpdate(createFunctionDDL);
	tableEnv.sqlUpdate(query);
	tableEnv.execute("Test job");

	Row[] result = TestCollectionTableFactory.RESULT().toArray(new Row[0]);
	Row[] expected = sourceData.toArray(new Row[0]);
	assertArrayEquals(expected, result);

	tableEnv.sqlUpdate("drop table t1");
	tableEnv.sqlUpdate("drop table t2");
}
 

public static void main(String[] args) throws Exception {

        EnvironmentSettings settings = EnvironmentSettings.newInstance()
                .useBlinkPlanner()
                .inStreamingMode()
                .build();
        // 创建一个使用 Blink Planner 的 TableEnvironment, 并工作在流模式
        TableEnvironment tEnv = TableEnvironment.create(settings);

        String kafkaSourceSql = "CREATE TABLE log (\n" +
                "    t INT, \n" +
                "    user_name VARCHAR,\n" +
                "    cnt INT\n" +
                ") WITH (\n" +
                "    'connector.type' = 'kafka',\n" +
                "    'connector.version' = 'universal',\n" +
                "    'connector.topic' = 'flink',\n" +
               "    'connector.startup-mode' = 'latest-offset',\n" +
                "    'connector.properties.0.key' = 'group.id',\n" +
                "    'connector.properties.0.value' = 'testGroup',\n" +
                "    'connector.properties.1.key' = 'bootstrap.servers',\n" +
                "    'connector.properties.1.value' = '192.168.56.103:9092',\n" +
                "    'connector.specific-offsets.0.partition' = '0',\n" +
                "    'connector.specific-offsets.0.offset' = '0',\n" +
                "    'update-mode' = 'append',\n" +
                "    'format.type' = 'json',\n" +
                "    'format.derive-schema' = 'true'\n" +
                ")";

        String mysqlSinkSql = "CREATE TABLE sink (\n" +
                "    t INT,\n" +
                "    user_name VARCHAR,\n" +
                "    total INT\n" +
                ") WITH (\n" +
                "    'connector.type' = 'jdbc',\n" +
                "    'connector.url' = 'jdbc:mysql://192.168.56.103:3306/flink',\n" +
                "    'connector.table' = 'log',\n" +
                "    'connector.username' = 'root',\n" +
                "    'connector.password' = '123456',\n" +
                "    'connector.write.flush.max-rows' = '1'\n" +
                ")";

        // 1. 连接kafka构建源表
        tEnv.sqlUpdate(kafkaSourceSql);


        // 2. 定义要输出的表
        tEnv.sqlUpdate(mysqlSinkSql);


        // 3. 自定义具体的 DML 操作

        tEnv.sqlUpdate("INSERT INTO sink " +
                "SELECT * from log where cnt=100");

        tEnv.execute("SQL Job");
    }
 

@Test
public void testWriteComplexType() throws Exception {
	String dbName = "default";
	String tblName = "dest";
	ObjectPath tablePath = new ObjectPath(dbName, tblName);

	TableSchema.Builder builder = new TableSchema.Builder();
	builder.fields(new String[]{"a", "m", "s"}, new DataType[]{
			DataTypes.ARRAY(DataTypes.INT()),
			DataTypes.MAP(DataTypes.INT(), DataTypes.STRING()),
			DataTypes.ROW(DataTypes.FIELD("f1", DataTypes.INT()), DataTypes.FIELD("f2", DataTypes.STRING()))});

	RowTypeInfo rowTypeInfo = createDestTable(dbName, tblName, builder.build(), 0);
	List<Row> toWrite = new ArrayList<>();
	Row row = new Row(rowTypeInfo.getArity());
	Object[] array = new Object[]{1, 2, 3};
	Map<Integer, String> map = new HashMap<Integer, String>() {{
		put(1, "a");
		put(2, "b");
	}};
	Row struct = new Row(2);
	struct.setField(0, 3);
	struct.setField(1, "c");

	row.setField(0, array);
	row.setField(1, map);
	row.setField(2, struct);
	toWrite.add(row);

	TableEnvironment tableEnv = HiveTestUtils.createTableEnv();
	Table src = tableEnv.fromTableSource(new CollectionTableSource(toWrite, rowTypeInfo));
	tableEnv.registerTable("complexSrc", src);

	tableEnv.registerCatalog("hive", hiveCatalog);
	tableEnv.sqlQuery("select * from complexSrc").insertInto("hive", "default", "dest");
	tableEnv.execute("mytest");

	List<String> result = hiveShell.executeQuery("select * from " + tblName);
	assertEquals(1, result.size());
	assertEquals("[1,2,3]\t{1:\"a\",2:\"b\"}\t{\"f1\":3,\"f2\":\"c\"}", result.get(0));

	hiveCatalog.dropTable(tablePath, false);
}