topic Re: Select a Percentage of Individuals in Data Quality

Select a Percentage of Individuals

curtisdee13 — Mon, 28 Jan 2019 11:36:37 GMT

Hi,

Does anyone know how to select a percentage of individuals from a selection using Talend components?

My aim is to select 5% of a selection and only insert that into my MDB table.

Thanks

Curtis

Re: Select a Percentage of Individuals

vapukov — Mon, 28 Jan 2019 12:12:16 GMT

hello,

you do not need open same topic twice (and more)

better - just proper describe, what is your task and problems?

it is not clear - what do you mean:

from a selection using - what is the selection? what is source of information
5% - is it any 5%, is it first 5%, is it strong random 5%?
individuals - what it means?

the more information you provide, the more chances to have a quick and correct suggestion.

cheers

Re: Select a Percentage of Individuals

curtisdee13 — Mon, 28 Jan 2019 12:45:16 GMT

Hi,

Thanks for replying. My task is to load a strong random 5% of rows from a group of rows that are being pulled back from a mySQL query i'm running. So for example if my mySQL query returns 1000 rows i'd like to only load 5% of that(50 rows) to the database.

The issue i currently have is when i try and use mySql to do this it never gives me exactly 5% it's usually in and around so i may get 40 rows or 60 rows. So my question is, is there anyway in talend to help me achieve this?

Hope the above explains my issue.

Re: Select a Percentage of Individuals

vapukov — Mon, 28 Jan 2019 12:59:46 GMT

something like this must work (in tMySQLInput):

set @rownum = (select count(*) from table_name)/20;
-- 20 is for 5%
set @row_num = 0;
SELECT * FROM (SELECT *, @row_num := @row_num + 1 AS row_num FROM table_name
ORDER BY RAND() ) t
WHERE row_num <= @rownum ;

possible for this you will need add into additional JDBC settings string:

allowMultiQueries=true

Re: Select a Percentage of Individuals

curtisdee13 — Tue, 29 Jan 2019 09:44:26 GMT

Hi,

Thanks for working this out, using the MySQL within the MySQLInput doesn't seem to be working but the query works fine when i run it from Navicat.

I also added in the

allowMultiQueries=true

line to the additional settings of the subjob.

Below is the current error i get when i run the job on Talend:

Exception in component tMysqlInput_4 (Mono19_23_ControlCellFlags)
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set @row_num = 0;
SELECT * FROM (SELECT personId,'C1_FALLOW' as control_cell, @r' at line 2
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4232)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4164)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
[statistics] disconnected
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2781)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1569)
at curtis_mono2019.mono19_23_controlcellflags_0_1.Mono19_23_ControlCellFlags.tMysqlInput_4Process(Mono19_23_ControlCellFlags.java:928)
at curtis_mono2019.mono19_23_controlcellflags_0_1.Mono19_23_ControlCellFlags.runJobInTOS(Mono19_23_ControlCellFlags.java:1527)
at curtis_mono2019.mono19_23_controlcellflags_0_1.Mono19_23_ControlCellFlags.main(Mono19_23_ControlCellFlags.java:1376)
Job Mono19_23_ControlCellFlags ended at 09:35 29/01/2019. [exit code=1]

Re: Select a Percentage of Individuals

vapukov — Tue, 29 Jan 2019 09:46:42 GMT

hi,

from log not seen full query text, so it hard to tell - what wrong

but it must work

Re: Select a Percentage of Individuals

curtisdee13 — Tue, 29 Jan 2019 09:59:45 GMT

Hi,

This is the full query i'm running, i've added in an extra field and an additional statement to the where clause but this runs fine on Navicat, can you see anything that would be an issue in the MySQLInput?

"set @rownum = (select count(*) from dataMono2019TodaysGameplay)/20;
set @row_num = 0;
SELECT * FROM (SELECT personId,'C1_FALLOW' as control_cell, @row_num := @row_num + 1 AS row_num FROM dataMono2019TodaysGameplay
ORDER BY RAND() ) t
WHERE row_num <= @rownum and personId not in (select personId from dataMono2019ControlCells);"

Thanks

Re: Select a Percentage of Individuals

vapukov — Tue, 29 Jan 2019 10:03:43 GMT

thank you, and what full JDBC additional string in your case?

Re: Select a Percentage of Individuals

curtisdee13 — Tue, 29 Jan 2019 10:13:05 GMT

Re: Select a Percentage of Individuals

vapukov — Tue, 29 Jan 2019 10:24:36 GMT

test with only & as separator (in you case and & and

Re: Select a Percentage of Individuals

curtisdee13 — Tue, 29 Jan 2019 10:32:35 GMT

Is this within my AdditionalJDBCSettings?

If so this is the error i get now:

Exception in component tMysqlInput_4 (Mono19_23_ControlCellFlags)
java.sql.SQLException: ResultSet is from UPDATE. No Data.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:6995)
at curtis_mono2019.mono19_23_controlcellflags_0_1.Mono19_23_ControlCellFlags.tMysqlInput_4Process(Mono19_23_ControlCellFlags.java:936)
at curtis_mono2019.mono19_23_controlcellflags_0_1.Mono19_23_ControlCellFlags.runJobInTOS(Mono19_23_ControlCellFlags.java:1527)
at curtis_mono2019.mono19_23_controlcellflags_0_1.Mono19_23_ControlCellFlags.main(Mono19_23_ControlCellFlags.java:1376)
[statistics] disconnected
Job Mono19_23_ControlCellFlags ended at 10:28 29/01/2019. [exit code=1]

Re: Select a Percentage of Individuals

vapukov — Tue, 29 Jan 2019 10:47:19 GMT

look for problem from this side - if query work in Navicat ... it must work there 🙂

step by step - no Connection Timeout as JDBC settings, it is - connectTimeout

try first just with allowMultiQueries=true, then add 1 by 1

Re: Select a Percentage of Individuals

curtisdee13 — Tue, 29 Jan 2019 11:17:52 GMT

I've had a look around online and found the below:

The real error here is:
Caused by: java.sql.SQLException: ResultSet is from UPDATE. No Data.

An SQL statement such the following will cause the error:

>>>
SET @myvar1 = CURDATE();
SET @myvar2 = DATE_SUB(@myvar1, INTERVAL 1 MONTH);
SELECT * FROM myTable WHERE myDate BETWEEN @myvar2 AND @myvar1;
<<<

The SET statements are effectively UPDATE queries which do not produce a result set. This is a trivial example to explain the error, more complex queries are likely to require this technique to reduce SQL code.

The issue seems to be within ReportControler.fillReport not correctly examining the returned result sets to see if they contain data. Only the last result set containing data should be used as the result set for the report. The code appears to assume that there is only one set returned and that it is to be used as the report dataset.

From this it seems the MySQLInput on Talend believes the set commands are update commands so it throws an error.

Do you know of anyway to get around this?

Re: Select a Percentage of Individuals

vapukov — Tue, 29 Jan 2019 11:20:03 GMT

sure, sorry was concentrated on SQL, but forget about tMySQLInput, of course - change it for tMySQLRow

Re: Select a Percentage of Individuals

curtisdee13 — Tue, 29 Jan 2019 12:23:07 GMT

Thanks i've given MySQLRow a go and joined it up to my tMap and i get the following error: