You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@flink.apache.org by tbud <te...@gmail.com> on 2021/04/17 00:20:17 UTC
Multiple select queries in single job on flink table API
If I want to run two different select queries on a flink table created from
the dataStream, the blink-planner runs them as two different jobs. Is there
a way to combine them and run as a single job ? Example code :
/StreamExecutionEnvironment env =
StreamExecutionEnvironment.getExecutionEnvironment();
env.setParallelism(4);
System.out.println("Running credit scores : ");
StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);
DataStream<String> recordsStream =
env.readTextFile("src/main/resources/credit_trial.csv");
DataStream<CreditRecord> creditStream = recordsStream
.filter((FilterFunction<String>) line -> !line.contains(
"Loan ID,Customer ID,Loan Status,Current Loan
Amount,Term,Credit Score,Annual Income,Years in current job" +
",Home Ownership,Purpose,Monthly Debt,Years of
Credit History,Months since last delinquent,Number of Open Accounts," +
"Number of Credit Problems,Current Credit
Balance,Maximum Open Credit,Bankruptcies,Tax Liens"))
.map(new MapFunction<String, CreditRecord>() {
@Override
public CreditRecord map(String s) throws Exception {
String[] fields = s.split(",");
return new CreditRecord(fields[0], fields[2],
Double.parseDouble(fields[3]),
fields[4], fields[5].trim().equals("")?0.0:
Double.parseDouble(fields[5]),
fields[6].trim().equals("")?0.0:Double.parseDouble(fields[6]),
fields[8], Double.parseDouble(fields[15]));
}
});
tableEnv.createTemporaryView("CreditDetails", creditStream);
Table creditDetailsTable = tableEnv.from("CreditDetails");
Table resultsTable = creditDetailsTable.select($("*"))
.filter($("loanStatus").isEqual("Charged Off"));
TableResult result = resultsTable.execute();
result.print();
Table resultsTable2 = creditDetailsTable.select($("*"))
.filter($("loanStatus").isEqual("Fully Paid"));
TableResult result2 = resultsTable2.execute();
result2.print();/
The above code creates 2 different jobs, but I don't want that, I want it to
run in a single job. Is there any way out ?
--
Sent from: http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/
Re: Multiple select queries in single job on flink table API
Posted by tbud <te...@gmail.com>.
/"TableResult result1 = stmtSet.execute();
result1.print();"/
I tried this, and the result is following :
Job has been submitted with JobID 4803aa5edc31b3ddc884f922008c5c03
+--------------------------------------------+--------------------------------------------+
| default_catalog.default_database.output1_1 |
default_catalog.default_database.output1_2 |
+--------------------------------------------+--------------------------------------------+
| -1 |
-1 |
+--------------------------------------------+--------------------------------------------+
1 row in set
Output folder is created but with empty files.
When I run my queries individually it gives me 68 and 107 rows respectively.
The problem starts happening when I add two statements to a StatementSet.
Has anybody faced this issue ? what was the solution ?
--
Sent from: http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/
Re: Multiple select queries in single job on flink table API
Posted by Arvid Heise <ar...@apache.org>.
Hi tbud,
you still have two executes; it should only be one. Can you try the
following instead of using outputTable1?
TableResult result1 = stmtSet.execute();
result1.print();
On Sun, Apr 18, 2021 at 12:05 AM tbud <te...@gmail.com> wrote:
> I have tried that too For example :
>
> /tableEnv.createTemporaryView("CreditDetails", creditStream);
> tableEnv.executeSql(
> "CREATE TABLE output(loanId VARCHAR) with ('connector.type' =
> 'filesystem',"
> + "'connector.path' = 'file:///path/Downloads/1',"
> + "'format.type' = 'csv')");
>
> Table creditDetailsTable =
> tableEnv.sqlQuery("SELECT id FROM CreditDetails where loanStatus = 'Charged
> Off'");
> Table creditDetailsTable2 =
> tableEnv.sqlQuery("SELECT id FROM CreditDetails where creditScore < 700");
>
> StatementSet stmtSet = tableEnv.createStatementSet();
> stmtSet.addInsert("output", creditDetailsTable);
> stmtSet.addInsert("output", creditDetailsTable2);
> stmtSet.execute();
>
> Table outputTable1 = tableEnv.from("output");
> Table resultsTable1 = outputTable1.select($("*"));
> TableResult result1 = resultsTable1.execute();
> result1.print();/
>
> But this prints result as 0 rows. What am I doing wrong ?
> Also the execution graph shows two different jobs getting created :
> collect,
> and insert. Can you help understand what's going on ?
>
>
>
> --
> Sent from:
> http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/
>
Re: Multiple select queries in single job on flink table API
Posted by tbud <te...@gmail.com>.
I have tried that too For example :
/tableEnv.createTemporaryView("CreditDetails", creditStream);
tableEnv.executeSql(
"CREATE TABLE output(loanId VARCHAR) with ('connector.type' = 'filesystem',"
+ "'connector.path' = 'file:///path/Downloads/1',"
+ "'format.type' = 'csv')");
Table creditDetailsTable =
tableEnv.sqlQuery("SELECT id FROM CreditDetails where loanStatus = 'Charged
Off'");
Table creditDetailsTable2 =
tableEnv.sqlQuery("SELECT id FROM CreditDetails where creditScore < 700");
StatementSet stmtSet = tableEnv.createStatementSet();
stmtSet.addInsert("output", creditDetailsTable);
stmtSet.addInsert("output", creditDetailsTable2);
stmtSet.execute();
Table outputTable1 = tableEnv.from("output");
Table resultsTable1 = outputTable1.select($("*"));
TableResult result1 = resultsTable1.execute();
result1.print();/
But this prints result as 0 rows. What am I doing wrong ?
Also the execution graph shows two different jobs getting created : collect,
and insert. Can you help understand what's going on ?
--
Sent from: http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/
Re: Multiple select queries in single job on flink table API
Posted by Yuval Itzchakov <yu...@gmail.com>.
Yes. Instead of calling execute on each table, create a StatementSet using
your StreamTableEnvironment (tableEnv.createStatementSet) and use addInsert
and finally .execute when you want to run the job.
On Sat, Apr 17, 2021, 03:20 tbud <te...@gmail.com> wrote:
> If I want to run two different select queries on a flink table created from
> the dataStream, the blink-planner runs them as two different jobs. Is there
> a way to combine them and run as a single job ? Example code :
>
> /StreamExecutionEnvironment env =
> StreamExecutionEnvironment.getExecutionEnvironment();
> env.setParallelism(4);
>
> System.out.println("Running credit scores : ");
>
> StreamTableEnvironment tableEnv = StreamTableEnvironment.create(env);
>
> DataStream<String> recordsStream =
> env.readTextFile("src/main/resources/credit_trial.csv");
>
> DataStream<CreditRecord> creditStream = recordsStream
> .filter((FilterFunction<String>) line -> !line.contains(
> "Loan ID,Customer ID,Loan Status,Current Loan
> Amount,Term,Credit Score,Annual Income,Years in current job" +
> ",Home Ownership,Purpose,Monthly Debt,Years of
> Credit History,Months since last delinquent,Number of Open Accounts," +
> "Number of Credit Problems,Current Credit
> Balance,Maximum Open Credit,Bankruptcies,Tax Liens"))
> .map(new MapFunction<String, CreditRecord>() {
>
> @Override
> public CreditRecord map(String s) throws Exception {
>
> String[] fields = s.split(",");
>
> return new CreditRecord(fields[0], fields[2],
> Double.parseDouble(fields[3]),
> fields[4], fields[5].trim().equals("")?0.0:
> Double.parseDouble(fields[5]),
>
> fields[6].trim().equals("")?0.0:Double.parseDouble(fields[6]),
> fields[8], Double.parseDouble(fields[15]));
> }
> });
> tableEnv.createTemporaryView("CreditDetails", creditStream);
> Table creditDetailsTable = tableEnv.from("CreditDetails");
>
> Table resultsTable = creditDetailsTable.select($("*"))
> .filter($("loanStatus").isEqual("Charged Off"));
>
> TableResult result = resultsTable.execute();
>
> result.print();
>
>
> Table resultsTable2 = creditDetailsTable.select($("*"))
> .filter($("loanStatus").isEqual("Fully Paid"));
>
> TableResult result2 = resultsTable2.execute();
>
> result2.print();/
>
> The above code creates 2 different jobs, but I don't want that, I want it
> to
> run in a single job. Is there any way out ?
>
>
>
> --
> Sent from:
> http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/
>