You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@flink.apache.org by po...@gmx.com on 2022/09/09 13:03:40 UTC

Insert into JDBC table

Why this INSERT does not insert row in table (jdbc connection works, I can create 'Customers' table from MySQL table)?
 
        tEnv.executeSql("CREATE TABLE Customers ("
        		 + " customer_number INT, "
        		 + " pid_no INT, "
        		 + " name STRING, "
        		 + " PRIMARY KEY (customer_number) NOT ENFORCED"
        		 + " ) WITH ( "
        		 + " 'connector' = 'jdbc', "
        		 + " 'url' = 'jdbc:mysql://localhost:3306/test', "
        		 + " 'username' = 'some_user', "
        		 + " 'table-name' = 'customers', "
        		 + " 'password' = ''"
        		+  ")");

        //This insert does nothing (not even error)
        tEnv.executeSql("INSERT INTO Customers (customer_number, pid_no, name) VALUES (4000, 100, 'customer')");

According to documentation (https://nightlies.apache.org/flink/flink-docs-master/docs/connectors/table/jdbc/) it should work.
Regards,

Mike

Re: Insert into JDBC table

Posted by yuxia <lu...@alumni.sjtu.edu.cn>.
"tEnv.executeSql("INSERT INTO Customers (customer_number, pid_no, name) VALUES (4000, 100, 'customer')");" should work. If not work, it seems to be a bug.

>> "Flink dynamic table is just a link to real data"
Yes, it's.

>> Is there any way to create empty table? Or table with some values defined in Flink?
Maybe you can try create table with Hive dialect[1] which enable you create a table in Hive using Flink SQL.
Or you can try flink-table-store.
AFAK, seems we can't create a table with some values defined directly.

[1] https://nightlies.apache.org/flink/flink-docs-master/docs/connectors/table/hive/hive_dialect/
[2] https://nightlies.apache.org/flink/flink-table-store-docs-master/
. Or

Best regards,
Yuxia

----- 原始邮件 -----
发件人: "podunk" <po...@gmx.com>
收件人: "User" <us...@flink.apache.org>
发送时间: 星期一, 2022年 9 月 12日 下午 8:36:54
主题: Re: Insert into JDBC table

I see I can only insert into JDBC table with select from another table, something like:
 
tEnv.executeSql("INSERT INTO Customers SELECT customer_number, pid_no, name FROM another_table");

But what if I want to insert row that I created within Flink? For instance I made some calculation and I want to insert completely new row into table (it does not exist in any table)? Something like:

tEnv.executeSql("INSERT INTO Customers (customer_number, pid_no, name) VALUES (4000, 100, 'customer')");

?

One more question - Flink dynamic table is just a link to real data (right?). Is there any way to create empty table? Or table with some values defined in Flink?

Thanks for help,

M.

 
 

Sent: Friday, September 09, 2022 at 3:03 PM
From: podunk@gmx.com
To: user@flink.apache.org
Subject: Insert into JDBC table
Why this INSERT does not insert row in table (jdbc connection works, I can create 'Customers' table from MySQL table)?
 
tEnv.executeSql("CREATE TABLE Customers ("
+ " customer_number INT, "
+ " pid_no INT, "
+ " name STRING, "
+ " PRIMARY KEY (customer_number) NOT ENFORCED"
+ " ) WITH ( "
+ " 'connector' = 'jdbc', "
+ " 'url' = 'jdbc:mysql://localhost:3306/test', "
+ " 'username' = 'some_user', "
+ " 'table-name' = 'customers', "
+ " 'password' = ''"
+ ")");

//This insert does nothing (not even error)
tEnv.executeSql("INSERT INTO Customers (customer_number, pid_no, name) VALUES (4000, 100, 'customer')");

According to documentation (https://nightlies.apache.org/flink/flink-docs-master/docs/connectors/table/jdbc/) it should work.
Regards,

Mike

Re: Insert into JDBC table

Posted by po...@gmx.com.
I see I can only insert into JDBC table with select from another table, something like:
 
tEnv.executeSql("INSERT INTO Customers SELECT customer_number, pid_no, name FROM another_table");

But what if I want to insert row that I created within Flink? For instance I made some calculation and I want to insert completely new row into table (it does not exist in any table)? Something like:

tEnv.executeSql("INSERT INTO Customers (customer_number, pid_no, name) VALUES (4000, 100, 'customer')");

?

One more question - Flink dynamic table is just a link to real data (right?). Is there any way to create empty table? Or table with some values defined in Flink?

Thanks for help,

M.

 
 

Sent: Friday, September 09, 2022 at 3:03 PM
From: podunk@gmx.com
To: user@flink.apache.org
Subject: Insert into JDBC table
Why this INSERT does not insert row in table (jdbc connection works, I can create 'Customers' table from MySQL table)?
 
tEnv.executeSql("CREATE TABLE Customers ("
+ " customer_number INT, "
+ " pid_no INT, "
+ " name STRING, "
+ " PRIMARY KEY (customer_number) NOT ENFORCED"
+ " ) WITH ( "
+ " 'connector' = 'jdbc', "
+ " 'url' = 'jdbc:mysql://localhost:3306/test', "
+ " 'username' = 'some_user', "
+ " 'table-name' = 'customers', "
+ " 'password' = ''"
+ ")");

//This insert does nothing (not even error)
tEnv.executeSql("INSERT INTO Customers (customer_number, pid_no, name) VALUES (4000, 100, 'customer')");

According to documentation (https://nightlies.apache.org/flink/flink-docs-master/docs/connectors/table/jdbc/) it should work.
Regards,

Mike