- users-list - monetdb.org

[MonetDB-users] Prepare statements as performance upgrade?
by Stefan de Konink 12 Jan '12

12 Jan '12

Hi, When I look into the executemany implementation of the Python DBAPI, I see that executemany is implemented as loop over the parameters given the same statement. In the mapi documentation there is written about using mapi_prepare_array() and mapi_execute_array(). Would such thing be beneficial in the general case? In my own program I have the following: while True: received = data_from_network() update_monetdb(received) It seems that the standard DBAPI doesn't expose prepared statements as some sort of transparent usage, so using SQL PREPARE sounds as the most logical way. What is the SQL statement to 'forget' the stored prepared statement? Stefan

1 0

[MonetDB-users] Disk space of a table
by Vassilis Papakonstantinou 11 Jan '12

11 Jan '12

Hello, Is there a way to get the disk space of a table? Vassilis

3 4

[MonetDB-users] how to load NaN as REAL
by bigu 10 Jan '12

10 Jan '12

I have a csv file and some columns are defined as REAL. The values some time have 'NaN' so when trying to load in the db it says cannot load NaN into REAL. COPY 1000000 OFFSET 2 RECORDS into mytable from 'C:\temp.csv' delimiters ',', '\n'; SQLException:importTable:value 'NaN' from line 11 field 10 not inserted, expecting type real failed to import table -- View this message in context: http://old.nabble.com/how-to-load-NaN-as-REAL-tp33107777p33107777.html Sent from the monetdb-users mailing list archive at Nabble.com.

3 2

[MonetDB-users] load CSV without header row
by bigu 09 Jan '12

09 Jan '12

Today installed monetdb for first time and wanted to play with some of the features but stuck with loading the file. Excuse me if this looks too basic. There is a 3GB csv file (1M rows and 400 columns). I am trying load in monetdb on my windows 7 machine. I first created a table mostly data is numeric exect for few that are metadata strings. How do i load this file without removing the header. The error says column_name not of type integer since the column_name is the actual header string but the values are integers. I can remove the header manually before loading but if i plan to scale then it is not a good option. Is there a way to say load "n" records from file starting at 'x' to 'y'? or ignore first line. COPY 1000000 RECORDS into mytable from 'C:\data.csv' delimiters ',', '\n'; Thanks. -- View this message in context: http://old.nabble.com/load-CSV-without-header-row-tp33096188p33096188.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 2

[MonetDB-users] [PATCH]: python-monetdb does not support RIPEMD160
by W-Mark Kubacki 09 Jan '12

09 Jan '12

Hello! Although MonetDB supports RIPEMD160, python-monetdb (version 11.5.9) does not. Patch [1] fixes that. Unfortunately Python's hashlib.algorithms and dir(hashlib) is "incomplete" in this regards and lists only algorithms which are guaranteed to be supported. Yet more are actually implemented. The patch moves the responsibility for checking whether RIPEMD160 (and others) is supported to hashlib, retaining the NotSupportedError. -- Mark Kubacki [1] https://raw.github.com/wmark/ossdl-overlay/master/dev-python/python-monetdb…

2 1

[MonetDB-users] Max Connection Limit
by Joseph Brower 06 Jan '12

06 Jan '12

Right now we are hitting the limit of 64 connections to MonetDB. We're working on some middleware that will consolidate those into only 2 or 3 connections, but until we finish it, we need to increase the limit. How do we go about doing that? Thanks, Joseph Brower

3 7

[MonetDB-users] Error in SELECT from a function via Mapi
by Iain Buchanan 02 Jan '12

02 Jan '12

Hi, I'm having problems calling a function via the Mapi interface (using Visual C++ under Windows and the 15th November download). It works fine when I call it directly, but fails when I call via Mapi. I create the function: create function tick.dummy(a integer) returns table (entry integer) begin return (select 1); end; I can then call this directly from mclient without any problems: sql>select entry from tick.dummy(1); +-------+ | entry | +=======+ | 1 | +-------+ 1 tuple (1.074ms) sql> However, if I call via Mapi I receive an error: SELECT: no such unary operator 'dummy(tinyint)' I'm using the following sequence of API calls: MapiHdl handle = mapi_prepare(connection, "SELECT entry FROM tick.dummy(?)"); int param(0); mapi_param_type(handle, 0, MAPI_INT, MAPI_AUTO, &param); if (mapi_execute(handle) != MOK) { // Error here } The above seems to work fine for a general SELECT statement, but fails for one with a function. Is there a different syntax for calling via Mapi? Iain

1 0

[MonetDB-users] ENUM style table with JOIN versus automatically deduplicated VARCHAR
by Stefan de Konink 02 Jan '12

02 Jan '12

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Hi, MonetDB 'deduplicates' strings, is there any benchmark that outlines the performance difference of an ENUM with a low number of (string)items versus the use of a VARCHAR field? The technical question would be; Is it more efficient to do: select * from my_table, my_enum where mytable.my_enum_id = my_enum.id and my_enum.name = 'Something'; or select * from my_table where my_enum_varchar = 'something'; I can imagine that the following would out perform a string comparison, but implies hardcoded values. select * from my_table, my_enum where mytable.my_enum_id = hardcoded; Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.18 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAk78fnkACgkQYH1+F2Rqwn3BUQCeOUtPIGzcHT1xpnsF4n78BQ/1 JWAAnjnzPeT8D7CtsjYgAiGg/WRIgNbQ =989f -----END PGP SIGNATURE-----

2 1

[MonetDB-users] MonetDB internals, does UPDATE update in place?
by Stefan de Konink 29 Dec '11

29 Dec '11

-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA512 Hi, Just to be very sure; I know that the DELETE, INSERT strategy to update the database will guarantee a database that keeps on growing, because of the lack of VACUUM. Is the normal UPDATE statement for fixed width datatypes in place, or is it also a form of copy-on-write? Stefan -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.18 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEAREKAAYFAk78fPwACgkQYH1+F2Rqwn2fxACfWdXvwr4pWODNo4ZH9uq5oVS9 BmwAn16XGh8h/vYsGkvthKej3MA8Nyvi =KK5T -----END PGP SIGNATURE-----

1 0

[MonetDB-users] Maximum size for SQL command in mclient
by Charalampos Nikolaou 28 Dec '11

28 Dec '11

Hi, is there a maximum size for an SQL command (e.g., INSERT INTO statement) that is imposed in mclient? The connection is terminated when I execute several commands from a sql file one of which is quite long. The point where I get the error is at the 8192th byte (which is the last of 8KBs). Babis

2 2

[MonetDB-users] protocol violation: unexpected line in data block
by tangray 27 Dec '11

27 Dec '11

the whole log file : RD 1298855541531: read final block: 57 bytes RX 1298855541531: onxRWd3Yv:mserver:9:RIPEMD160,SHA256,SHA1,MD5:LIT:SHA512: RD 1298855541531: inserting prompt TD 1298855541531: write final block: 92 bytes TX 1298855541531: BIG:risk:{SHA256}01fa94fefcadb5918473e49aaa8e425538f8d40960b2d649348cb17c599031ae:sql:krisk: RD 1298855541546: read final block: 0 bytes RX 1298855541546: RD 1298855541546: inserting prompt TD 1298855541546: write final block: 48 bytes TX 1298855541546: sSET TIME ZONE INTERVAL '+08:00' HOUR TO MINUTE; RD 1298855541546: read final block: 0 bytes RX 1298855541546: RD 1298855541546: inserting prompt TD 1298855541546: write final block: 15 bytes TX 1298855541546: Xreply_size 250 RD 1298855541546: read final block: 0 bytes RX 1298855541546: RD 1298855541546: inserting prompt TD 1298855541546: write final block: 46 bytes TX 1298855541546: sSELECT "name", "value" FROM sys.env() as env; RD 1298855541546: read final block: 4246 bytes RX 1298855541546: &1 0 46 2 46 % .env, .env # table_name % name, value # name % varchar, varchar # type % 18, 203 # length [ "gdk_arch", "32biti686-pc-win32" ] [ "gdk_version", "1.40.3" ] [ "prefix", "D:\\Program Files\\MonetDB\\MonetDB5" ] [ "exec_prefix", "D:\\Program Files\\MonetDB\\MonetDB5" ] [ "gdk_dbname", "krisk" ] [ "gdk_dbfarm", "C:\\Documents and Settings\\swordtan\\Application Data\\MonetDB5\\dbfarm" ] [ "gdk_debug", "0" ] [ "gdk_alloc_map", "no" ] [ "gdk_vmtrim", "yes" ] [ "monet_admin", "adm" ] [ "monet_prompt", ">" ] [ "monet_welcome", "yes" ] [ "monet_mod_path", "D:\\Program Files\\MonetDB\\MonetDB5\\lib\\MonetDB5;D:\\Program Files\\MonetDB\\MonetDB5\\lib\\MonetDB5\\lib;D:\\Program Files\\MonetDB\\MonetDB5\\lib\\MonetDB5\\bin" ] [ "monet_daemon", "no" ] [ "host", "localhost" ] [ "mapi_port", "50000" ] [ "mapi_clients", "2" ] [ "mapi_open", "true" ] [ "mapi_autosense", "false" ] [ "default_pipe", "inline,remap,evaluate,costModel,coercions,emptySet,aliases,mitosis,mergetable,deadcode,commonTerms,joinPath,reorder,deadcode,reduce,dataflow,history,multiplex,garbageCollector" ] [ "minimal_pipe", "inline,remap,deadcode,multiplex,garbageCollector" ] [ "sql_optimizer", "default_pipe" ] [ "sql_debug", "0" ] [ "standoff_ns", "" ] [ "standoff_start", "start" ] [ "standoff_end", "end" ] [ "config", "D:\\Program Files\\MonetDB\\MonetDB5\\etc\\monetdb5.conf" ] [ "mero_pidfile", "D:\\Program Files\\MonetDB\\MonetDB5\\var\\run\\MonetDB\\merovingian.pid" ] [ "mero_controlport", "50001" ] [ "nov2009_pipe", "inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,multiplex,garbageCollector" ] [ "replication_pipe", "inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,replication,multiplex,garbageCollector" ] [ "accumulator_pipe", "inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,accumulators,dataflow,history,multiplex,garbageCollector" ] [ "recycler_pipe", "inline,remap,evaluate,costModel,coercions,emptySet,aliases,deadcode,constants,commonTerms,joinPath,deadcode,recycle,reduce,dataflow,history,multiplex,garbageCollector" ] [ "cracker_pipe", "inline,remap,evaluate,costModel,coercions,emptySet,aliases,selcrack,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,multiplex,garbageCollector" ] [ "sidcrack_pipe", "inline,remap,evaluate,costModel,coercions,emptySet,aliases,sidcrack,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,multiplex,garbageCollector" ] [ "datacell_pipe", "inline,remap,evaluate,costModel,coercions,emptySet,aliases,deadcode,constants,commonTerms,joinPath,datacell,deadcode,reduce,dataflow,history,multiplex,garbageCollector" ] [ "octopus_pipe", "inline,remap,evaluate,costModel,coercions,emptySet,aliases,mitosis,mergetable,deadcode,constants,commonTerms,joinPath,octopus,deadcode,reduce,dataflow,history,multiplex,garbageCollector" ] [ "mapreduce_pipe", "inline,remap,evaluate,costModel,coercions,emptySet,aliases,mapreduce,mergetable,deadcode,commonTerms,joinPath,reorder,deadcode,reduce,dataflow,history,multiplex,garbageCollector" ] [ "datacyclotron_pipe", "inline,remap,evaluate,costModel,coercions,emptySet,aliases,datacyclotron,mergetable,deadcode,constants,commonTerms,joinPath,reorder,deadcode,reduce,dataflow,history,replication,multiplex,garbageCollector" ] [ "derive_pipe", "inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,derivePath,joinPath,deadcode,reduce,dataflow,history,multiplex,garbageCollector" ] [ "dictionary_pipe", "inline,remap,dictionary,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,history,multiplex,garbageCollector" ] [ "compression_pipe", "inline,remap,evaluate,costModel,coercions,emptySet,aliases,mergetable,deadcode,constants,commonTerms,joinPath,deadcode,reduce,dataflow,compression,dataflow,history,multiplex,garbageCollector" ] [ "gdk_mem_pagebits", "16" ] [ "gdk_mem_bigsize", "1048576" ] [ "monet_pid", "984" ] [ "monet_version", "5.22.3" ] RD 1298855541546: inserting prompt TD 1298855541562: write final block: 60 bytes TX 1298855541562: sselect * from risk.td_ind_security_held where id = 1560000; RD 1298855541578: read final block: 1600 bytes RX 1298855541578: &1 1 1 30 1 % risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held # table_name % id, bizdate, painnercode, currency, exchange, seccode, basickind, unrealizedgainchange, unrealizedgain, margingain, interestgain, othergain, yieldrate, accrualinterest, allgain, fullmv, marketvalue, lastfullmv, lastmarketvalue, lastcost, cost, buysum, sellsum, realizedgain, holdingqty, lastholdingqty, mvweight, lastmvweight, fullmvweight, lastfullmvweight # name % int, varchar, varchar, varchar, varchar, varchar, varchar, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal # type % 7, 8, 8, 3, 5, 6, 3, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 14, 14, 14, 14 # length RD 1298855541578: inserting prompt TD 1298855762109: write final block: 60 bytes TX 1298855762109: sselect * from risk.td_ind_security_held where id = 1560000; RD 1298855762125: read final block: 1600 bytes RX 1298855762125: &1 2 1 30 1 % risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held # table_name % id, bizdate, painnercode, currency, exchange, seccode, basickind, unrealizedgainchange, unrealizedgain, margingain, interestgain, othergain, yieldrate, accrualinterest, allgain, fullmv, marketvalue, lastfullmv, lastmarketvalue, lastcost, cost, buysum, sellsum, realizedgain, holdingqty, lastholdingqty, mvweight, lastmvweight, fullmvweight, lastfullmvweight # name % int, varchar, varchar, varchar, varchar, varchar, varchar, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal, decimal # type % 7, 8, 8, 3, 5, 6, 3, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 20, 14, 14, 14, 14 # length RD 1298855762125: inserting prompt TD 1298856279453: write final block: 69 bytes TX 1298856279453: sselect id,bizdate from risk.td_ind_security_held where id = 1560000; RD 1298856279453: read final block: 162 bytes RX 1298856279453: &1 3 1 2 1 % risk.td_ind_security_held, risk.td_ind_security_held # table_name % id, bizdate # name % int, varchar # type % 7, 8 # length [ 1560000, "20090702" ] RD 1298856279453: inserting prompt TD 1298856279468: write final block: 1797 bytes TX 1298856279468: sSELECT 'krisk' AS "TABLE_CAT", "schemas"."name" AS "TABLE_SCHEM", "tables"."name" AS "TABLE_NAME", "columns"."name" AS "COLUMN_NAME", cast(CASE "columns"."type" WHEN 'wrd' THEN -5 WHEN 'sec_interval' THEN -5 WHEN 'bigint' THEN -5 WHEN 'timetz' THEN 92 WHEN 'char' THEN 1 WHEN 'int' THEN 4 WHEN 'clob' THEN 2005 WHEN 'month_interval' THEN 4 WHEN 'tinyint' THEN -6 WHEN 'table' THEN 2003 WHEN 'date' THEN 91 WHEN 'double' THEN 8 WHEN 'decimal' THEN 3 WHEN 'timestamptz' THEN 93 WHEN 'timestamp' THEN 93 WHEN 'blob' THEN 2004 WHEN 'real' THEN 7 WHEN 'time' THEN 92 WHEN 'oid' THEN 1111 WHEN 'boolean' THEN 16 WHEN 'smallint' THEN 5 WHEN 'varchar' THEN 12 ELSE 1111 END AS smallint) AS "DATA_TYPE", "columns"."type" AS "TYPE_NAME", "columns"."type_digits" AS "COLUMN_SIZE", "columns"."type_scale" AS "DECIMAL_DIGITS", 0 AS "BUFFER_LENGTH", 10 AS "NUM_PREC_RADIX", cast(CASE "null" WHEN true THEN 1 WHEN false THEN 0 END AS int) AS "NULLABLE", cast(null AS varchar(1)) AS "REMARKS", "columns"."default" AS "COLUMN_DEF", 0 AS "SQL_DATA_TYPE", 0 AS "SQL_DATETIME_SUB", 0 AS "CHAR_OCTET_LENGTH", "columns"."number" + 1 AS "ORDINAL_POSITION", cast(null AS varchar(1)) AS "SCOPE_CATALOG", cast(null AS varchar(1)) AS "SCOPE_SCHEMA", cast(null AS varchar(1)) AS "SCOPE_TABLE", cast(1111 AS smallint) AS "SOURCE_DATA_TYPE", CASE "null" WHEN true THEN CAST ('YES' AS varchar(3)) WHEN false THEN CAST ('NO' AS varchar(3)) END AS "IS_NULLABLE" FROM "sys"."columns" AS "columns", "sys"."tables" AS "tables", "sys"."schemas" AS "schemas" WHERE "columns"."table_id" = "tables"."id" AND "tables"."schema_id" = "schemas"."id" AND LOWER("schemas"."name") LIKE 'risk' AND LOWER("tables"."name") LIKE 'td_ind_security_held' AND LOWER("columns"."name") LIKE 'id' ORDER BY "TABLE_SCHEM", "TABLE_NAME", "ORDINAL_POSITION"; RD 1298856279484: read final block: 853 bytes RX 1298856279484: &1 4 1 22 1 % ., sys.schemas, .tables, .columns, ., .columns, .columns, .columns, ., ., ., ., .columns, ., ., ., ., ., ., ., ., . # table_name % TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, DECIMAL_DIGITS, BUFFER_LENGTH, NUM_PREC_RADIX, NULLABLE, REMARKS, COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH, ORDINAL_POSITION, SCOPE_CATALOG, SCOPE_SCHEMA, SCOPE_TABLE, SOURCE_DATA_TYPE, IS_NULLABLE # name % char, varchar, varchar, varchar, smallint, varchar, int, int, tinyint, tinyint, int, varchar, varchar, tinyint, tinyint, tinyint, int, varchar, varchar, varchar, smallint, varchar # type % 5, 4, 20, 2, 1, 3, 2, 1, 1, 2, 1, 0, 0, 1, 1, 1, 1, 0, 0, 0, 4, 2 # length [ "krisk", "risk", "td_ind_security_held", "id", 4, "int", 32, 0, 0, 10, 0, NULL, NULL, 0, 0, 0, 1, NULL, NULL, NULL, 1111, "NO" ] RD 1298856279484: inserting prompt TD 1298856279500: write final block: 1802 bytes TX 1298856279500: sSELECT 'krisk' AS "TABLE_CAT", "schemas"."name" AS "TABLE_SCHEM", "tables"."name" AS "TABLE_NAME", "columns"."name" AS "COLUMN_NAME", cast(CASE "columns"."type" WHEN 'wrd' THEN -5 WHEN 'sec_interval' THEN -5 WHEN 'bigint' THEN -5 WHEN 'timetz' THEN 92 WHEN 'char' THEN 1 WHEN 'int' THEN 4 WHEN 'clob' THEN 2005 WHEN 'month_interval' THEN 4 WHEN 'tinyint' THEN -6 WHEN 'table' THEN 2003 WHEN 'date' THEN 91 WHEN 'double' THEN 8 WHEN 'decimal' THEN 3 WHEN 'timestamptz' THEN 93 WHEN 'timestamp' THEN 93 WHEN 'blob' THEN 2004 WHEN 'real' THEN 7 WHEN 'time' THEN 92 WHEN 'oid' THEN 1111 WHEN 'boolean' THEN 16 WHEN 'smallint' THEN 5 WHEN 'varchar' THEN 12 ELSE 1111 END AS smallint) AS "DATA_TYPE", "columns"."type" AS "TYPE_NAME", "columns"."type_digits" AS "COLUMN_SIZE", "columns"."type_scale" AS "DECIMAL_DIGITS", 0 AS "BUFFER_LENGTH", 10 AS "NUM_PREC_RADIX", cast(CASE "null" WHEN true THEN 1 WHEN false THEN 0 END AS int) AS "NULLABLE", cast(null AS varchar(1)) AS "REMARKS", "columns"."default" AS "COLUMN_DEF", 0 AS "SQL_DATA_TYPE", 0 AS "SQL_DATETIME_SUB", 0 AS "CHAR_OCTET_LENGTH", "columns"."number" + 1 AS "ORDINAL_POSITION", cast(null AS varchar(1)) AS "SCOPE_CATALOG", cast(null AS varchar(1)) AS "SCOPE_SCHEMA", cast(null AS varchar(1)) AS "SCOPE_TABLE", cast(1111 AS smallint) AS "SOURCE_DATA_TYPE", CASE "null" WHEN true THEN CAST ('YES' AS varchar(3)) WHEN false THEN CAST ('NO' AS varchar(3)) END AS "IS_NULLABLE" FROM "sys"."columns" AS "columns", "sys"."tables" AS "tables", "sys"."schemas" AS "schemas" WHERE "columns"."table_id" = "tables"."id" AND "tables"."schema_id" = "schemas"."id" AND LOWER("schemas"."name") LIKE 'risk' AND LOWER("tables"."name") LIKE 'td_ind_security_held' AND LOWER("columns"."name") LIKE 'bizdate' ORDER BY "TABLE_SCHEM", "TABLE_NAME", "ORDINAL_POSITION"; RD 1298856279515: read final block: 862 bytes RX 1298856279515: &1 5 1 22 1 % ., sys.schemas, .tables, .columns, ., .columns, .columns, .columns, ., ., ., ., .columns, ., ., ., ., ., ., ., ., . # table_name % TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, DECIMAL_DIGITS, BUFFER_LENGTH, NUM_PREC_RADIX, NULLABLE, REMARKS, COLUMN_DEF, SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH, ORDINAL_POSITION, SCOPE_CATALOG, SCOPE_SCHEMA, SCOPE_TABLE, SOURCE_DATA_TYPE, IS_NULLABLE # name % char, varchar, varchar, varchar, smallint, varchar, int, int, tinyint, tinyint, int, varchar, varchar, tinyint, tinyint, tinyint, int, varchar, varchar, varchar, smallint, varchar # type % 5, 4, 20, 7, 2, 7, 1, 1, 1, 2, 1, 0, 0, 1, 1, 1, 1, 0, 0, 0, 4, 2 # length [ "krisk", "risk", "td_ind_security_held", "bizdate", 12, "varchar", 8, 0, 0, 10, 0, NULL, NULL, 0, 0, 0, 2, NULL, NULL, NULL, 1111, "NO" ] RD 1298856279515: inserting prompt TD 1298856508906: write final block: 77 bytes TX 1298856508906: sselect id,bizdate,allgain from risk.td_ind_security_held where id = 1560000; RD 1298856508906: read final block: 187 bytes RX 1298856508906: &1 6 1 3 1 % risk.td_ind_security_held, risk.td_ind_security_held, risk.td_ind_security_held # table_name % id, bizdate, allgain # name % int, varchar, decimal # type % 7, 8, 20 # length RD 1298856508906: inserting prompt

2 1

[MonetDB-users] java.lang.AssertionError: block 0 should have been fetched by now
by Viktor Rosenfeld 27 Dec '11

27 Dec '11

Hi, I get an error message when I retrieve a MAL plan over JDBC after the 250th call to ResultSet.next(). The relevant part of the stack trace is: java.lang.AssertionError: block 0 should have been fetched by now :( at nl.cwi.monetdb.jdbc.MonetConnection$ResultSetResponse.getLine(MonetConnection.java:1585) at nl.cwi.monetdb.jdbc.MonetResultSet.absolute(MonetResultSet.java:194) at nl.cwi.monetdb.jdbc.MonetResultSet.relative(MonetResultSet.java:2149) at nl.cwi.monetdb.jdbc.MonetResultSet.next(MonetResultSet.java:2116) The call to EXPLAIN works in mclient and returns a MAL plan that is 402 lines long. I can reproduce the error when I try to create the plan via jdbcclient, except that it says that block 1 should have been fetched and the exporter stops after line 160 or so. However, other SQL queries that also produce more than 250 results work over JDBC. Not sure what the difference is, but these queries only return column of integers. I'm using the Aug2011-SP3 release on OS X 10.6.8. Please let me know if I should provide other information like the SQL query or the MAL plan. Cheers, Viktor

2 3

[MonetDB-users] time series data scaling
by David Pennell 20 Dec '11

20 Dec '11

In a large scale time series row-store DB (Oracle), I'm accustomed to partitioning fact tables on time. This has has a couple of benefits: - since queries always include predicates on the time field, partition elimination can help a lot - when the time based partitions map one-to-one with tablespaces, removing old data is extremely fast. (when you've reached the maximum data retention period, you need to remove data as fast as you can load it). Are there any features in monetdb that would facilitate these needs (or ameliorate them)? TIA, -david

2 2

[MonetDB-users] Benefits of sorted columns
by Viktor Rosenfeld 19 Dec '11

19 Dec '11

Hi, I know that when a table is sorted on a column, value look-ups on that column will use a binary search instead of a linear search. Is there a similar benefit for joins? E.g. if I have 2 tables that contain a sorted column C and I do an equality join on this column (or a self-join using 2 aliases of the same table), I would expect a traditional DBMS to do a merge-join. Cheers, Viktor

2 2

[MonetDB-users] SQLException:SQLengine:Program contains errors
by Charalampos Nikolaou 17 Dec '11

17 Dec '11

Hi, I am getting the error "SQLException:SQLengine:Program contains errors" when I define a simple function that returns a table. The SQL reference on the site of MonetDB (http://www.monetdb.org/Documentation/SQLreference/Flowofcontrol) states that the accepted expression to a "TABLE" expression should be "select_no_parens_orderby" which is not defined in any place (or haven't found it) other than sql_parser.y. The function I define is the following and apart from the fact that is inserted successfully into the "functions" table, I checked that it can be produced by the grammar in sql_parser.y: CREATE FUNCTION refine () RETURNS TABLE (u varchar(32), v varchar(32), r varchar(5)) BEGIN RETURN TABLE ( ( SELECT R.u, R.v, R.r FROM R INTERSECT SELECT R.u, R.v, R.r FROM R ) ); END; Further, what is the proper way to deleting a function, in order to re-define it in the same session? I observed that is not sufficient to just delete it from sys.functions table. An entry #function_name is also present in sys._tables. Thanks, Babis

3 5

Re: [MonetDB-users] Information request
by Ying Zhang 16 Dec '11

16 Dec '11

On Fri, Dec 16, 2011 at 10:51:18PM +0800, dj zhang wrote: > Hello Jennie, > > Glad to hear from you! Thanks for your reply! I'm sorry that I didn't > realize that you guys are in a new year vacation. Hello Dongjie, I'd like to recommend you to subscribe to the MonetDB users mailling list: http://www.monetdb.org/Developers/Mailinglists , and sent your feature questions to the mailinglist. This way, your questions will reach the whole developer team at one, and you'll most probably get answers faster. I felt free to forward this e-mail to the mailing list. > Maybe I didn't express myself very clear. I've generated a table file in > which each line represents a record, and the fields of a record are > separated by "|", e.g. "abc|1.2.3.3|www.monetdb.org|3". I can bulk load > this file to MonetDB by SQL COPY INTO command. Unfortunately, you can't load this table using the binary bulk load feature of MonetDB, since this feature only handles numerical data, and doesn't not work with strings. How large (in GBs) is your total dataset? Maybe you don't have to use the binary bulk load feature... > Now I want to change the table file to column files, each file just > contains one column's values which also stored line by line. I want to know > if I can load this table by loading these column files. > > From your information, Can I say that I have to change these column files > to binary format that MonetDB recognise(BAT storage), then use binary bulk > load command to load the table to MonetDB. And if I change the files to > other binary format it will not work. Is that right? That's right. MonetDB just works with one binary format. Regards, Jennie > > Thank you so much! Once again happy new year! > > Best wishes, > > Dongjie > ------------------------------------- > > On Fri, Dec 16, 2011 at 8:55 PM, Ying Zhang <Y.Zhang(a)cwi.nl> wrote: > > > Hello Zhang Dongjie, > > > > First of all, thank you very much for your interest in MonetDB. > > > > > I have learn from the website document that I can load data use column > > > files when migrate between MonetDB instances, but the file has to be in > > the > > > format of binary version of the BAT storage if I didn't misunderstand. > > > > If each column of a table is already stored in binary format in a file, > > the table can be loaded using the "binary bulk load" feature of MonetDB, > > as described here: > > http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/BinaryBulkLoad > > > > If you are migrating your existing database to an exactly the same > > machine, you can just copy dbfarm sub-directory for your database. If > > you are migrating to a machine with different hardware, you need to dump > > the database and reload it on the destination machine, as described here: > > http://www.monetdb.org/Documentation/UserGuide/DumpRestore > > However, in this case, the dumped data can not be loaded using the > > binary bulk load feature, because the file containing dumpted data is an > > ASCII file. > > > > > So my question is, is there any way to load data to monetdb by column as > > > long as the record is store by column. Or do I have to first change the > > > column files to the binary version of BAT format then use the Binary Bulk > > > Load commands. > > > > You can only use the binary bulk load feature, iff the data of each > > column is stored in a separate file in binary format. Hence, yes, > > you'll have to generate those binary files, if you don't have them yet. > > However, if your data is already in, e.g., CSV format, you don't have to > > create binary files for the columns, since you can just load the CSV > > file. If your (CSV) data is not too large, the standard SQL COPY INTO > > feature should just work. > > > > > And if I have to change the format, is there any way to use google > > protobuf > > > to serialize the column file to the BAT format, or is there any solutions > > > like handlersocket for mysql? > > > > I'm unfortunately not familiar with protobuf and handlersocket. Maybe > > someone else in our group can answer these questions. > > > > Hope this helps a bit. > > > > > > With kind regads, > > > > Jennie Zhang > > > > > > > > > > By the way I think MonetDB is a great project and I wish it can find it's > > > way to commercialize very soon! > > > > > > Thank you very much, and Merry Christmas! > > > > > > Best regards, > > > > > > Dongjie > > > ------------------ > > > Dongjie Zhang > > > > > > Services Computing Technology and System Lab, > > > Cluster and Grid Computing Lab, > > > School of Computer Science and Technology, > > > Huazhong University of Science and Technology, > > > Wuhan, 430074, China > > > > > > 华中科技大学计算机科学与技术学院 > > > 服务计算技术与系统教育部重点实验室集群与网格计算湖北省重点实验室 > > > 湖北武汉 430074 > > > > > _______________________________________________ > > > Info mailing list > > > Info(a)monetdb.org > > > http://mail.monetdb.org/mailman/listinfo/info > > > > > > > -- > ------------------ > Dongjie Zhang > > Services Computing Technology and System Lab, > Cluster and Grid Computing Lab, > School of Computer Science and Technology, > Huazhong University of Science and Technology, > Wuhan, 430074, China > > 华中科技大学计算机科学与技术学院 > 服务计算技术与系统教育部重点实验室集群与网格计算湖北省重点实验室 > 湖北武汉 430074

1 0

[MonetDB-users] Invalid BAT if left join condition has only columns from right table
by George Garbis 15 Dec '11

15 Dec '11

Hello to all, a left join which have only right table columns in join condition seems to produce invalid bat. Assume we have the tables below: CREATE TABLE tableA(x integer, y integer); INSERT INTO tableA values(1,10); INSERT INTO tableA values(2,20); CREATE TABLE tableB(x integer, y integer); INSERT INTO tableB values(1,-10); INSERT INTO tableB values(2,-20); and pose the query: SELECT A.x, A.y, B.x, B.y FROM tableA A LEFT JOIN tableB B ON ( B.y < -10 ) ; the expected results are: +------+----- +------+------+ | x | y | x | y | +===+===+===+===+ | 1 | 10 | 2 | -20 | | 2 | 20 | 2 | -20 | +------+------+------+------+ but mclient prints this message: TypeException:user.s1_1[36]:'algebra.kdifference' undefined in: _55:any := algebra.kdifference(_54:bat[:oid,:int], _44:bat[:void,:oid]) TypeException:user.s1_1[37]:'algebra.markT' undefined in: _56:any := algebra.markT(_55:any, _21:oid) TypeException:user.s1_1[38]:'bat.reverse' undefined in: _57:any := bat.reverse(_56:any) TypeException:user.s1_1[39]:'algebra.join' undefined in: _58:any := algebra.join(_57:any, _20:bat[:oid,:int]) TypeException:user.s1_1[41]:'algebra.leftjoin' undefined in: _60:any := algebra.leftjoin(_59:bat[:oid,:void], _54:bat[:oid,:int]) TypeException:user.s1_1[43]:'bat.append' undefined in: _64:any := bat.append(_61:bat[:oid,:int], _60:any, _65:bit) TypeException:user.s1_1[44]:'bat.append' undefined in: _66:any := bat.append(_64:any, _58:any, _65:bit) TypeException:user.s1_1[55]:'algebra.join' undefined in: _80:any := algebra.join(_57:any, _79:bat[:oid,:int]) TypeException:user.s1_1[61]:'algebra.leftjoin' undefined in: _90:any := algebra.leftjoin(_59:bat[:oid,:void], _89:bat[:oid,:int]) TypeException:user.s1_1[63]:'bat.append' undefined in: _92:any := bat.append(_91:bat[:oid,:int], _90:any, _65:bit) TypeException:user.s1_1[64]:'bat.append' undefined in: _93:any := bat.append(_92:any, _80:any, _65:bit) TypeException:user.s1_1[70]:'algebra.project' undefined in: _102:any := algebra.project(_57:any, _100:int) TypeException:user.s1_1[83]:'bat.append' undefined in: _119:any := bat.append(_117:bat[:oid,:int], _102:any, _118:bit) TypeException:user.s1_1[89]:'algebra.project' undefined in: _126:any := algebra.project(_57:any, _125:int) TypeException:user.s1_1[96]:'bat.append' undefined in: _133:any := bat.append(_132:bat[:oid,:int], _126:any, _118:bit) SQLException:SQLengine:Program contains errors For ease I attach sql scripts for creating tables and posing the query Best regards, George Garbis

2 1

[MonetDB-users] ODBC Stops inserting
by Joseph Brower 13 Dec '11

13 Dec '11

Hello everyone! We're using the odbc driver to insert records every half second or so. These records are for analytics information about visitors to various websites that we host. (We found MonetDB to be very fast for fetching information like average request time, number of visitors, general analytics information like that.) We've run into a few issues though. The biggest concern is that we stop seeing new records entered after a while. The log shows 2011-12-13 17:00:00 MSG merovingian[86]: proxying client 10.60.5.13:37359 for database 'web_requests' to mapi:monetdb:///var/lib/monetdb/web_requests/web_requests/.mapi.sock?database=web_requests 2011-12-13 17:00:00 MSG merovingian[86]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying over and over for each machine that would be inserting these records. But no new records are ever added. Another concern is that in our installation, we don't see a MonetDB.conf file. I was hoping that we could specify how much RAM we'd like to allow MonetDB to consume. This is to ensure that our memory manager doesn't kill MonetDB, since that's the primary role of this server. Is this a file that we should create? I'm still fairly new to MonetDB and as a result, am still trying to find my way with it. The last concern I have is the log file size. Is there a way (once we've ironed out the before mentioned issues) to instruct MonetDB to only log things of a certain importance or higher? For example, to only log ERR rather than ERR and MSG messages? Our log files will become quite large and unmanageable if we don't disable or implement some sort of log rotation system. The version of MonetDB that we are running is MonetDB 5 server v11.5.7 "Aug2011-SP2". We are running under Ubuntu 10.04 64Bit. Thanks, Joseph Brower

2 3

[MonetDB-users] SpagoBI
by Jeremy OSullivan 09 Dec '11

09 Dec '11

Hello,We are trying to get SpagoBI to work with MonetDB. SpagoBI uses a JDBC interface. The problem is as follows: The import of the schema works fine. However, when you save the model in SpagoBI, the schema in MonetDB can no longer be viewed. We have tried this with MySQL and it works fine.Regards,Jeremy

1 0

[MonetDB-users] Can't create a csv vault?
by Mark Aufflick 08 Dec '11

08 Dec '11

Hi all, I'm very interested in the vault feature of monetdb, both for csv and for creating my own formats. Trouble is I can't make a simple example work. I've flattended the voc sample data into a csv file, and then try to load it so: create table test (type varchar(255), number integer, number_sup varchar(255), trip integer, trip_sup varchar(255), onboard_at_departure integer, death_at_cap integer, left_at_cape integer, onboard_at_cape integer, death_during_voyage integer, onboard_at_arrival integer) external csv '/tmp/all.csv'; But I get an error: syntax error, unexpected EXTERNAL, expecting SCOLON in: ... Do I need to enable vaults at configure time or create time or something? I didn't see an option for it. I built from the "Latest" tarball on OSX Lion. Cheers, Mark.

2 3

[MonetDB-users] stethoscope on OS X can't connect to database
by Viktor Rosenfeld 07 Dec '11

07 Dec '11

Hi, when I try to connect to a database with stethoscope I get an error: $ stethoscope -d annis_test -u annis_test -P ... +tis stethoscope: no databases found for 'annis_test' I'm using the Aug2011-SP3 release on OS X 10.6.8. The same command works under Linux. The instances are set up identically. Cheers, Viktor

2 2

[MonetDB-users] JDBC documentation inconsistencies
by Viktor Rosenfeld 29 Nov '11

29 Nov '11

Hi, the example at the top of the documentation of the JDBC client at http://www.monetdb.org/Documentation/Cookbooks/SQLrecipies/Clients/JDBC uses the -d switch to specify the table and the -b switch to specify the database. However the current version does not recognize the -b switch and the -d switch can be used to select the database as is documented further on the page. Also, the page says that TRACE is not possible via JDBC. However, as the jdbcclient.jar demonstrates it is indeed possible to use TRACE on a query by checking additional result sets. Cheers, Viktor

2 4

[MonetDB-users] Announcement: New Aug2011-SP3 Bugfix release of MonetDB suite
by Fabian Groffen 22 Nov '11

22 Nov '11

The MonetDB team at CWI/MonetDB BV is pleased to announce the Aug2011-SP3 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at http://www.monetdb.org/. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Note that this is the last release of the Aug2011 branch. From now on, no further development will take place on this branch. Aug 2011-SP3 bugfix release SQL * Added a fix for bug #2834, which caused weird (failing) behaviour with PreparedStatements. Merovingian * Fixed a bug where monetdbd's socket files from /tmp were removed when a second monetdbd was attempted to be started using the same port. Bug Fixes * 2875: incomplete database initialization * 2912: Function body disappears after loading data - error message: SQLException:sql.resultSet:Cannot access descriptor * 2919: Add column and select in same transaction * 2920: Updated database from Aug2011 to Dec2011 make queries fail with "Subquery result missing". * 2921: complex where clause broken * 2923: Multiplication and cast from decimal to integer leads to 0 value * 2924: use LIKE with ESCAPE ignores OR operator * 2928: Cannot update a field with a count query based on an other table

1 0

[MonetDB-users] Workflow for independent, read only, discretely updated tables.
by Hedge Hog 20 Nov '11

20 Nov '11

Hi, I'm evaluating MonetDB, and the following is based on reading the docs and some of the monetdb-users email list archive. I wonder if the following setup+workflow is correct, and if so, is it monetdb best practice, given this use case: We will have read only tables, each distributed across several machines/servers, they will be updated daily. There will be no cross table queries, i.e. only one table touched by each quert. We would like to be able to update each table while not affecting the availability of any other table. The table_db's are not horizontally sharded, i.e. all the data for a query will always come from that one table. We cannot use udp multicast/broadcast so a monetdb cluster is not possible (unless a localhost cluster is possible/sensible?). The setup+workflow: - one table per database (this allows for independent table updates), let these be <table_db>. - update a master (writable) instance of the table on a 'special' monetdbd/machine. - copy the updated table to each machine. - To update the table on a machine: $> monetbd lock <mytable_db> $> monetbd stop <mytable_db> $> mclient -u monetdb <mytable_db> updatefile $> cat updatefile copy into MyTable from ('path_to_mytable_col_file_i', 'path_to_mytable_col_file_f', 'path_to_mytable_col_file_s'); $> monetbd release <mytable_db> $> monetbd start <mytable_db> Is the above the best pattern/architecture of monetdb for such a use case? Appreciate any insights people can offer TIA Hedge -- πόλλ' οἶδ ἀλώπηξ, ἀλλ' ἐχῖνος ἓν μέγα [The fox knows many things, but the hedgehog knows one big thing.] Archilochus, Greek poet (c. 680 BC – c. 645 BC) http://hedgehogshiatus.com

3 4

[MonetDB-users] Unable to use Tableau
by Sékine Coulibaly 18 Nov '11

18 Nov '11

Hi there, I'm a new user to both Tableau and MonetDb. I run under Windows, and my MonetDb base is up and running, a table "matable" was created and populated. Although I can connect the MonetDb base to Tableau (although I get some warnings), as soon as Tableau sends a request to MonetDb, the ODBC driver returns the following : « Database error (0x80004005) : [MonetDB][ODBC Driver 11.5.7]Invalid identifier 'none:name:nk' » As can be seen here : http://puu.sh/8STw Has any of you already used Tableau and solved this issue ? Shall I consider using Pentaho ? Thank you ! Regards

2 1