Select a Percentage of Individuals

[curtisdee13]

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

[vapukov]

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

[curtisdee13]

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.

[vapukov]

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

[curtisdee13]

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]

[vapukov]

hi,

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

but it must work

[curtisdee13]

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

[vapukov]

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

[curtisdee13]

[vapukov]

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