Another related question: I am writing to a large database that has 2.1 billion rows of genomic data. As I add columns, I create a binary file for each, recreate the table with the new columns, and load it all. This has been great - everything loads very fast. This week I’ve needed to add 2 columns of text data, which are of length 3 and 8. This is the first time I’ve added non-numeric data to this table. The fix below allowed me to create the table, but mnonetdb believes the column sizes are wrong when loading. The file sizes look correct. I am adding data of size byte, integer, char(3) and char(8). The sizes of my files are below: -rwxrwxrwx 1 lcj34 11103514 2059943587 May 10 16:29 teLTR_chromo.bin* (this was byte data) -rwxrwxrwx 1 lcj34 11103514 2059943587 May 10 16:29 teLTR_envr.bin* (also byte data) -rwxrwxrwx 1 lcj34 11103514 8239774348 May 10 16:29 teLTR_ltr.bin* ( this is an integer column) -rwxrwxrwx 1 lcj34 11103514 8239774348 May 10 16:29 teLTR_nestLevel.bin* (also integer column) -rwxrwxrwx 1 lcj34 11103514 6179830761 May 10 16:29 teLTR_sf.bin* (this is my char(3) column) -rwxrwxrwx 1 lcj34 11103514 16479548696 May 10 16:29 teLTR_fam.bin* ( this is my char(8) column) These sizes look correct. The integer column size of 8239774348 is 4 times the byte column. The char(3) column size of 6179830761 is 3 times the byte column size, and the char(8) column size of 16479548696 is 8 times the byte column size. When I Load the byte data (teLTR_chromo.bin and teLTR_envr.bin) it correctly loads 2059943587 rows. When I load the integer values I also correctly get 2059943587 rows loaded. Monetdb, however, complains the size is wrong for the 2 character columns. I loaded each of the character columns into a table with just that column to see how many rows Monetdb thought were there. It shows only 5895 rows for the binary file made up of char(3) values, and 15720 rows for the binary file made up of char(8) values: sql>COPY binary into justSuperFamily from ('/workdir/lcj34/monetdbFiles/michelle_TE/teLTR_sf.bin'); 5895 affected rows (39.4s) sql> sql>copy binary into justfamily from ('/workdir/lcj34/monetdbFiles/michelle_TE/teLTR_fam.bin'); 15720 affected rows (3m 17s) sql> What would I be doing incorrectly in terms of creating these columns? My java code to create the binary files looks like this: String defaultSF = "OOO"; writerSF.writeChars(defaultSF.getBytes()); String defaultFam = "OOOOOOOO"; writerFam.writeChars(defaultFam.getBytes()); My writers extend FilterOutputStream, the writeChars() method looks like this: public void writeChars(byte[] data) throws IOException { ByteBuffer buffer = ByteBuffer.allocate(data.length).order(ByteOrder.LITTLE_ENDIAN); buffer.put(data); out.write(buffer.array()); } Is there something else that needs to be added? Do I need new lines or something to separate the distinct values? When monetdb sees a variable defined as “char(3)” does it read more than 3 bytes? Thanks for any insight - Lynn On 5/11/16, 6:49 AM, "users-list on behalf of Lynn Carol Johnson" wrote:

Thank you - Sorry I didn¹t notice the [ vs ) when I looked at the types. This worked.

On 5/10/16, 5:51 PM, "users-list on behalf of Stefan Manegold" wrote:

...

Hi,

you need to omit the single quotes that are used to indicate literal symbols (as opposed to keywords) in the SQL syntax definition at https://www.monetdb.org/Documentation/Manuals/SQLreference/BuiltinTypes

i.e., instead of "char '('3')'" and "char'('8')'" the correct SQL syntax is "char (3)" and "char(8)".

Best, Stefan

----- On May 10, 2016, at 11:06 PM, Lynn Carol Johnson lcj34@cornell.edu wrote:

...

Hi All -

I¹m trying to create a table with specific size for text fields. In particular, I am storing a 3 byte character and an 8 byte character. I tried the following but get errors:

CREATE TABLE te_LTRTest(chr int, pos int, te_gag_protein tinyint, te_ap_protein tinyint, te_integrase_protein tinyint, te_rt_protein tinyint, te_rnaseh_protein tinyint, te_chromodomain_protein tinyint, te_envelope_protein tinyint, te_ltr_age real, te_numb_ltrtes_inside integer , te_superfamily char '('3')' , te_family char '('8') ¹ );

Gives the error:

sql>\

syntax error, unexpected STRING, expecting ')' or ',' in: "create table te_ltrtest(chr int, pos int, te_gag_protein tinyint, te_ap_protein"

sql>

Switching to this:

CREATE TABLE te_LTRTest(chr int, pos int, te_gag_protein tinyint, te_ap_protein tinyint, te_integrase_protein tinyint, te_rt_protein tinyint, te_rnaseh_protein tinyint, te_chromodomain_protein tinyint, te_envelope_protein tinyint, te_ltr_age real, te_numb_ltrtes_inside integer , te_superfamily char [ 3 ], te_family char [ 8 ] );

Gives the following error:

sql>\

syntax error, unexpected '[', expecting ')' or ',' in: "create table te_ltrtest(chr int, pos int, te_gag_protein tinyint, te_ap_protein"

sql>

I have 2.1 billion rows so am using the binary bulk loads. My non-numeric binary files were created to contain 3-byte values and 8-byte values. This is what I am trying to load, however I¹m not getting past the create table stage. I love the binary loader. This is the first time I¹ve needed to include text data in my tables. Is this supported? I¹m hoping it is just my syntax.

Any suggestions?

Thanks - Lynn

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Thanks - I have it working now. On 5/11/16, 7:42 AM, "users-list on behalf of Martin Kersten" wrote:

See the documentation: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/BinaryBulkLoad

"For variable length strings, the file must have one C-based string value per line, terminated by a newline, and it is processed without escape character conversion. "

On 11/05/16 13:38, Lynn Carol Johnson wrote:

...

Another related question:

I am writing to a large database that has 2.1 billion rows of genomic data. As I add columns, I create a binary file for each, recreate the table with the new columns, and load it all. This has been great - everything loads very fast.

This week I’ve needed to add 2 columns of text data, which are of length 3 and 8. This is the first time I’ve added non-numeric data to this table. The fix below allowed me to create the table, but mnonetdb believes the column sizes are wrong when loading. The file sizes look correct. I am adding data of size byte, integer, char(3) and char(8). The sizes of my files are below:

-rwxrwxrwx 1 lcj34 11103514 2059943587 May 10 16:29 teLTR_chromo.bin* (this was byte data) -rwxrwxrwx 1 lcj34 11103514 2059943587 May 10 16:29 teLTR_envr.bin* (also byte data) -rwxrwxrwx 1 lcj34 11103514 8239774348 May 10 16:29 teLTR_ltr.bin* ( this is an integer column) -rwxrwxrwx 1 lcj34 11103514 8239774348 May 10 16:29 teLTR_nestLevel.bin* (also integer column) -rwxrwxrwx 1 lcj34 11103514 6179830761 May 10 16:29 teLTR_sf.bin* (this is my char(3) column) -rwxrwxrwx 1 lcj34 11103514 16479548696 May 10 16:29 teLTR_fam.bin* ( this is my char(8) column)

These sizes look correct. The integer column size of 8239774348 is 4 times the byte column. The char(3) column size of 6179830761 is 3 times the byte column size, and the char(8) column size of 16479548696 is 8 times the byte column size.

When I Load the byte data (teLTR_chromo.bin and teLTR_envr.bin) it correctly loads 2059943587 rows. When I load the integer values I also correctly get 2059943587 rows loaded.

Monetdb, however, complains the size is wrong for the 2 character columns. I loaded each of the character columns into a table with just that column to see how many rows Monetdb thought were there. It shows only 5895 rows for the binary file made up of char(3) values, and 15720 rows for the binary file made up of char(8) values:

sql>COPY binary into justSuperFamily from ('/workdir/lcj34/monetdbFiles/michelle_TE/teLTR_sf.bin'); 5895 affected rows (39.4s) sql>

sql>copy binary into justfamily from ('/workdir/lcj34/monetdbFiles/michelle_TE/teLTR_fam.bin'); 15720 affected rows (3m 17s) sql>

What would I be doing incorrectly in terms of creating these columns? My java code to create the binary files looks like this:

String defaultSF = "OOO"; writerSF.writeChars(defaultSF.getBytes()); String defaultFam = "OOOOOOOO"; writerFam.writeChars(defaultFam.getBytes());

My writers extend FilterOutputStream, the writeChars() method looks like this:

public void writeChars(byte[] data) throws IOException { ByteBuffer buffer = ByteBuffer.allocate(data.length).order(ByteOrder.LITTLE_ENDIAN); buffer.put(data); out.write(buffer.array()); }

Is there something else that needs to be added? Do I need new lines or something to separate the distinct values? When monetdb sees a variable defined as “char(3)” does it read more than 3 bytes?

Thanks for any insight - Lynn

On 5/11/16, 6:49 AM, "users-list on behalf of Lynn Carol Johnson" wrote:

...

Thank you - Sorry I didn¹t notice the [ vs ) when I looked at the types. This worked.

On 5/10/16, 5:51 PM, "users-list on behalf of Stefan Manegold" wrote:

...

Hi,

you need to omit the single quotes that are used to indicate literal symbols (as opposed to keywords) in the SQL syntax definition at

https://www.monetdb.org/Documentation/Manuals/SQLreference/BuiltinTypes

i.e., instead of "char '('3')'" and "char'('8')'" the correct SQL syntax is "char (3)" and "char(8)".

Best, Stefan

----- On May 10, 2016, at 11:06 PM, Lynn Carol Johnson lcj34@cornell.edu wrote:

...

Hi All -

I¹m trying to create a table with specific size for text fields. In particular, I am storing a 3 byte character and an 8 byte character. I tried the following but get errors:

CREATE TABLE te_LTRTest(chr int, pos int, te_gag_protein tinyint, te_ap_protein tinyint, te_integrase_protein tinyint, te_rt_protein tinyint, te_rnaseh_protein tinyint, te_chromodomain_protein tinyint, te_envelope_protein tinyint, te_ltr_age real, te_numb_ltrtes_inside integer , te_superfamily char '('3')' , te_family char '('8') ¹ );

Gives the error:

sql>\

syntax error, unexpected STRING, expecting ')' or ',' in: "create table te_ltrtest(chr int, pos int, te_gag_protein tinyint, te_ap_protein"

sql>

Switching to this:

CREATE TABLE te_LTRTest(chr int, pos int, te_gag_protein tinyint, te_ap_protein tinyint, te_integrase_protein tinyint, te_rt_protein tinyint, te_rnaseh_protein tinyint, te_chromodomain_protein tinyint, te_envelope_protein tinyint, te_ltr_age real, te_numb_ltrtes_inside integer , te_superfamily char [ 3 ], te_family char [ 8 ] );

Gives the following error:

sql>\

syntax error, unexpected '[', expecting ')' or ',' in: "create table te_ltrtest(chr int, pos int, te_gag_protein tinyint, te_ap_protein"

sql>

I have 2.1 billion rows so am using the binary bulk loads. My non-numeric binary files were created to contain 3-byte values and 8-byte values. This is what I am trying to load, however I¹m not getting past the create table stage. I love the binary loader. This is the first time I¹ve needed to include text data in my tables. Is this supported? I¹m hoping it is just my syntax.

Any suggestions?

Thanks - Lynn

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list