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

The following examples show how to use org.apache.flink.table.api.TableEnvironment#execute() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: TableEnvHiveConnectorTest.java    From flink with Apache License 2.0 6 votes vote down vote up
@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");
}
 
Example 2
Source File: TableEnvHiveConnectorTest.java    From flink with Apache License 2.0 6 votes vote down vote up
@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");
	}
}
 
Example 3
Source File: HiveTableSinkTest.java    From flink with Apache License 2.0 6 votes vote down vote up
@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);
}
 
Example 4
Source File: HiveTableSinkTest.java    From flink with Apache License 2.0 6 votes vote down vote up
@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);
}
 
Example 5
Source File: BatchSQLTestProgram.java    From flink with Apache License 2.0 6 votes vote down vote up
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");
}
 
Example 6
Source File: CatalogITest.java    From pulsar-flink with Apache License 2.0 5 votes vote down vote up
@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()));
}
 
Example 7
Source File: CatalogITest.java    From pulsar-flink with Apache License 2.0 5 votes vote down vote up
@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()));
}
 
Example 8
Source File: TableEnvHiveConnectorTest.java    From flink with Apache License 2.0 5 votes vote down vote up
@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");
}
 
Example 9
Source File: TableEnvHiveConnectorTest.java    From flink with Apache License 2.0 5 votes vote down vote up
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");
}
 
Example 10
Source File: HiveTableSinkTest.java    From flink with Apache License 2.0 5 votes vote down vote up
@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);
}
 
Example 11
Source File: HiveTableSinkTest.java    From flink with Apache License 2.0 5 votes vote down vote up
@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);
}
 
Example 12
Source File: HiveTableSinkTest.java    From flink with Apache License 2.0 5 votes vote down vote up
@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);
}
 
Example 13
Source File: FunctionITCase.java    From flink with Apache License 2.0 5 votes vote down vote up
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");
}
 
Example 14
Source File: SqlConnect.java    From flink-simple-tutorial with Apache License 2.0 4 votes vote down vote up
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");
    }
 
Example 15
Source File: HiveTableSinkTest.java    From flink with Apache License 2.0 4 votes vote down vote up
@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);
}