- users-list - monetdb.org

mapi connect latency
by Matthew Russell 22 Jan '14

22 Jan '14

Hi all, I am writing a simple C++ dll which uses the mapi interface to connect to MonetDB. The query itself runs extremely fast which is fantastic. I did notice that the mapi_connect() takes approximately 1 second to connect on my machine on a local socket. Is there anyway to improve this connection latency? I can't unfortunately use a connection pool or keep the connection open due to the design of the calling application. Best regards, Matthew Russell

3 5

Re: users-list Digest, Vol 17, Issue 8
by Edgar Mejia 22 Jan '14

22 Jan '14

Hello Jennie, I can to do clustering, to use (map-reduce, handoop) as monetdb for large amounts of information? any have any documentation for this? What amount of information are you talking about? I have two applications. In 1 i have over 100.000.000 millions of records. And i have other application as more of 400.000.000 millions of records and i need migrate this of oracle. Thanks, Edgar M 2014/1/22 <users-list-request(a)monetdb.org> > Send users-list mailing list submissions to > users-list(a)monetdb.org > > To subscribe or unsubscribe via the World Wide Web, visit > https://www.monetdb.org/mailman/listinfo/users-list > or, via email, send a message with subject or body 'help' to > users-list-request(a)monetdb.org > > You can reach the person managing the list at > users-list-owner(a)monetdb.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of users-list digest..." > > > Today's Topics: > > 1. RE: problem with Jan2014 build (Bas Kaptijn) > 2. Re: What differences between monetdb and mongodb for > analytical data? (Ying Zhang) > > > ---------------------------------------------------------------------- > > Message: 1 > Date: Tue, 21 Jan 2014 11:55:38 +0000 > From: Bas Kaptijn <b.kaptijn(a)chs.nl> > To: "users-list(a)monetdb.org" <users-list(a)monetdb.org> > Subject: RE: problem with Jan2014 build > Message-ID: > < > a553ef932478438e82367f954b701a0f(a)AMSPR05MB211.eurprd05.prod.outlook.com> > > Content-Type: text/plain; charset="us-ascii" > > Some last info: the problem disappears when using the no_mitosis_pipe > optimizer pipeline. > > sql>set optimizer='no_mitosis_pipe'; > operation successful (0.139ms) > sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET > 400000; > > +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ > | file > | line | source > > | > > +=============================================================================+========+====================================================================================================================================================+ > | > /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC > | 430664 | 04999999999999999999999999999999999999999 > > : > > +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ > 1 tuple (1.1s) > sql>set optimizer='default_pipe'; > operation successful (0.305ms) > sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET > 400000; > +------+------+--------+ > | file | line | source | > +======+======+========+ > +------+------+--------+ > 0 tuples (798.870ms) > > Regards, Bas >

1 0

What differences between monetdb and mongodb for analytical data?
by Edgar Mejia 21 Jan '14

21 Jan '14

I had 6 months works as monetdb and this is great for analytical data, datawarehouses and cubes. Which is faster of these two databases in percentage for reading? I can to do clustering, to use (map-reduce, handoop) as monetdb for large amounts of information? any have any documentation for this? Thanks, Edgar M

2 1

RE: problem with Jan2014 build
by Bas Kaptijn 21 Jan '14

21 Jan '14

Some last info: the problem disappears when using the no_mitosis_pipe optimizer pipeline. sql>set optimizer='no_mitosis_pipe'; operation successful (0.139ms) sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | file | line | source | +=============================================================================+========+====================================================================================================================================================+ | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | 430664 | 04999999999999999999999999999999999999999 : +-----------------------------------------------------------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 1 tuple (1.1s) sql>set optimizer='default_pipe'; operation successful (0.305ms) sql>SELECT * FROM source1 WHERE SUBSTRING(SOURCE,1,2)='04' LIMIT 1 OFFSET 400000; +------+------+--------+ | file | line | source | +======+======+========+ +------+------+--------+ 0 tuples (798.870ms) Regards, Bas

1 0

RE: problem with Jan2014 build
by Bas Kaptijn 21 Jan '14

21 Jan '14

Ok got the Jan2014 package working now with the comments in #3419 as suggested. Thanks. I omitted the LOCKED statement and now the count of rows per value in the column "file" matches the amount of rows in the actual files that were loaded, so that seems to work. But it dows not solve the whole problem. I still get the initial reported problem with querying only the rows of which the first two characters in the value of the field 'source' matches the string '04'. When I select a count of rows it reports about 2.8M matching rows which it should. When selecting the actual rows however I get a lot less. sql>SELECT COUNT(*), file FROM source1 GROUP BY file; +---------+-----------------------------------------------------------------------------+ | L1 | file | +=========+=============================================================================+ | 540299 | /home/bkaptijn/DDDB/.tmpinputdir/9723_9999_20130301_20130331_Schade_516.ASC | | 1655861 | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | | 476629 | /home/bkaptijn/DDDB/.tmpinputdir/9787_9999_20130401_20130430_Schade_516.ASC | | 886713 | /home/bkaptijn/DDDB/.tmpinputdir/9788_9999_20130601_20130630_Schade_516.ASC | | 1404888 | /home/bkaptijn/DDDB/.tmpinputdir/9795_9999_20130701_20130731_Schade_516.ASC | | 1509934 | /home/bkaptijn/DDDB/.tmpinputdir/9978_9999_20130801_20130831_Schade_516.ASC | +---------+-----------------------------------------------------------------------------+ 6 tuples (94.898ms) sql>SELECT COUNT(*) FROM source1 WHERE SUBSTRING(source,1,2) = '04'; +---------+ | L1 | +=========+ | 2842771 | +---------+ 1 tuple (901.377ms) sql>SELECT * FROM source1 WHERE SUBSTRING(source,1,2) = '04' LIMIT 1 OFFSET 326371; +-----------------------------------------------------------------------------+--------+----------------------------------------------+ | file | line | source +=============================================================================+========+==============================================+ | /home/bkaptijn/DDDB/.tmpinputdir/9772_7084_20130501_20130531_Schade_516.ASC | 268991 | 0499999999999999999999999999999999999999999 | +-----------------------------------------------------------------------------+--------+----------------------------------------------+ 1 tuple (770.827ms) sql>SELECT * FROM source1 WHERE SUBSTRING(source,1,2) = '04' LIMIT 1 OFFSET 326372; +------+------+--------+ | file | line | source | +======+======+========+ +------+------+--------+ 0 tuples (892.431ms) sql>CREATE TABLE test04 AS SELECT * FROM source1 WHERE SUBSTRING(source,1,2) = '04' WITH DATA; operation successful (21.4s) sql>SELECT COUNT(*) FROM test04; +--------+ | L1 | +========+ | 326372 | +--------+ Note: I didn't have this problem with the Feb2013-SP5 release. I never tested the Feb2013-SP6 release. Regards Bas

1 0

RE: problem with Jan2014 build
by Bas Kaptijn 21 Jan '14

21 Jan '14

Additional info: A build of the latest sources of the Feb2013 branch shows the same problem. However when I omit the optional LOCKED statement in the COPY INTO queries the problem does not occur. It seems the LOCKED statement also does not improve the performance of loading data much anymore (in my case at least); I can omit it but the data loading is as fast as before, so I guess this works for me now, but still there seems to be a problem with this LOCKED data loading. Regards, Bas

1 0

problem with Jan2014 build
by Bas Kaptijn 20 Jan '14

20 Jan '14

Hi, With a build of the latest version in the Jan2014 branch I encounter a problem which worked fine in the Feb2013-SP5 release. I load multiple files into a single table with a COPY INTO ... LOCKED statement for each file. This seems to work correctly but When quering this table I get the following results (note I masked sensitive data with 9's but that does not matter): bkaptijn@DevRef1:~/DDDB$ mclient -d cacis Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased) Database: MonetDB v11.17.2 (unreleased), 'mapi:monetdb://DevRef1:50000/cacis' Type \q to quit, \? for a list of available commands auto commit mode: on sql>SELECT COUNT(*) FROM source1; +---------+ | L1 | +=========+ | 6474324 | +---------+ 1 tuple (0.662ms) sql>SELECT COUNT(*), file FROM source1 GROUP BY file; +---------+-----------------------------------------------------------------------------+ | L1 | file | +=========+=============================================================================+ | 809290 | /home/bkaptijn/DDDB/.tmpinputdir/9723_9999_20130301_20130331_Schade_516.ASC | | 1618580 | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | | 809290 | /home/bkaptijn/DDDB/.tmpinputdir/9787_9999_20130401_20130430_Schade_516.ASC | | 809290 | /home/bkaptijn/DDDB/.tmpinputdir/9788_9999_20130601_20130630_Schade_516.ASC | | 1618580 | /home/bkaptijn/DDDB/.tmpinputdir/9795_9999_20130701_20130731_Schade_516.ASC | | 809294 | /home/bkaptijn/DDDB/.tmpinputdir/9978_9999_20130801_20130831_Schade_516.ASC | +---------+-----------------------------------------------------------------------------+ 6 tuples (13.204ms) sql>\q bkaptijn@DevRef1:~/DDDB$ wc -l /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC 1655861 /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC bkaptijn@DevRef1:~/DDDB$ wc -l /home/bkaptijn/DDDB/.tmpinputdir/9723_9999_20130301_20130331_Schade_516.ASC 540299 /home/bkaptijn/DDDB/.tmpinputdir/9723_9999_20130301_20130331_Schade_516.ASC bkaptijn@DevRef1:~/DDDB$ wc -l /home/bkaptijn/DDDB/.tmpinputdir/*.ASC 540299 /home/bkaptijn/DDDB/.tmpinputdir/9723_9999_20130301_20130331_Schade_516.ASC 1655861 /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC 476629 /home/bkaptijn/DDDB/.tmpinputdir/9787_9999_20130401_20130430_Schade_516.ASC 886713 /home/bkaptijn/DDDB/.tmpinputdir/9788_9999_20130601_20130630_Schade_516.ASC 1404888 /home/bkaptijn/DDDB/.tmpinputdir/9795_9999_20130701_20130731_Schade_516.ASC 1509934 /home/bkaptijn/DDDB/.tmpinputdir/9978_9999_20130801_20130831_Schade_516.ASC 6474324 total bkaptijn@DevRef1:~/DDDB$ mclient -d cacis Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased) Database: MonetDB v11.17.2 (unreleased), 'mapi:monetdb://DevRef1:50000/cacis' Type \q to quit, \? for a list of available commands auto commit mode: on sql>SELECT COUNT(*) FROM source1 WHERE SUBSTRING(source,1,2) = '04'; +---------+ | L1 | +=========+ | 2842771 | +---------+ 1 tuple (1.1s) sql>SELECT * FROM source1 WHERE SUBSTRING(source,1,2) = '04' LIMIT 1 OFFSET 326372; +------+------+--------+ | file | line | source | +======+======+========+ +------+------+--------+ 0 tuples (752.096ms) sql>SELECT * FROM source1 WHERE SUBSTRING(source,1,2) = '04' LIMIT 1 OFFSET 326371; +-----------------------------------------------------------------------------+--------+-------------------------------------------------------------------------------------------+ | file | line | source | +=============================================================================+========+===========================================================================================+ | /home/bkaptijn/DDDB/.tmpinputdir/9772_9999_20130501_20130531_Schade_516.ASC | 268991 | 04999999999999999999999999999999999999999999999999999999999999999999999 | +-----------------------------------------------------------------------------+--------+-------------------------------------------------------------------------------------------+ 1 tuple (832.778ms) This can't be right. I formerly compiled, build and used the Feb2013 from the mercurial site without problems. bkaptijn@DevRef1:~/DDDB$ mserver5 --version MonetDB 5 server v11.17.2 (64-bit, 64-bit oids) This is an unreleased version Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 15.6GiB available memory, 8 available cpu cores Libraries: libpcre: 8.12 2011-01-15 (compiled with 8.12) openssl: OpenSSL 1.0.1 14 Mar 2012 (compiled with OpenSSL 1.0.1 14 Mar 2012) libxml2: 2.7.8 (compiled with 2.7.8) Compiled by: bkaptijn@DevRef1 (x86_64-unknown-linux-gnu) Compilation: gcc -O3 -pipe -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code -Wp,-D_FORTIFY_SOURCE=2 Linking : /usr/bin/ld -m elf_x86_64 Is this a bug? Regards, Bas Kaptijn PS I also tried to install the Jan2014 RC1 but I can't get it working on my Ubuntu (precise) system. First it misses libstream6.so while a libstream3.so was installed... when creating a symlink referring to the libstream3 lib it fails to startup mentioning that it can not setup communication channel. The Merovingian log mentions a MALException that says that it can not write to the "querylog" (which I also can not find in the dbfarm where I though it should be) and a MALException something about sysmon.

2 2

Concurrent connections to MonetDB and temporary tables
by Alastair McKinley 15 Jan '14

15 Jan '14

Dear all, I want to connect to a single MonetDB server from an application using the mapi interface. The intention is to load a small input dataset using insert statements and mapi_query(). Then I will execute a SELECT statement from mapi against another dataset resident in the MonetDB server and return the results to the application. My question is about possible concurrent connections from this application. Is it sufficient to use unique table names for my input data sets so each input data set ends up in a separate table? Is there a better approach for this use case built in to MonetDB that I have missed? Best regards, Alastair

1 0

Announcement: Release Candidate for Jan2014 Release
by Sjoerd Mullender 15 Jan '14

15 Jan '14

The MonetDB team at CWI/MonetDB BV is pleased to announce a release candidate for the upcoming Jan2014 feature release. Many things have changed in this release compared to the previous (Feb2013) release (that's why it took so long). For a smooth upgrade I would urge you to try out this release candidate and to provide feedback on our bug tracker (http://bugs.monetdb.org) The release candidate can be found at http://dev.monetdb.org/downloads/testing. -- Sjoerd Mullender

1 0

MonetDB licensing
by Basil Peace 15 Jan '14

15 Jan '14

Currently MonetDB is licensed under `fork` of MPL 1.1. This is free software license, but it is not compatible with GPL ( http://www.gnu.org/licenses/license-list.html#MPL ). Are there some plans to change license, e.g. to upgrade to MPL 2.0? I'm asking since I'm planning big project (under GPLv3) just now, and I need to choose backend database. I like MonetDB, but GPL incompatibility stopped me. Best regards, Basil Peace

2 3

Stay in touch with me through LinkedIn
by Edgar Mejia silva 14 Jan '14

14 Jan '14

LinkedIn ------------ Communication: Me gustaría añadirte a mi red profesional en LinkedIn. -Edgar Edgar Mejia silva Ingeniero consultor de sistemas de gestion y BI (Business intelligent) at ITFusion Colombia Confirm that you know Edgar Mejia silva: https://www.linkedin.com/e/-id7iby-hqfkq7w7-3s/isd/18454372725/QZ9x6cIx/?hs… -- You are receiving Invitation to Connect emails. Click to unsubscribe: http://www.linkedin.com/e/-id7iby-hqfkq7w7-3s/d3mz0XtXuHhwR56ylf8k7AOIRLhmN… (c) 2012 LinkedIn Corporation. 2029 Stierlin Ct, Mountain View, CA 94043, USA.

1 0

Building a debug version of mserver5?
by Alastair McKinley 14 Jan '14

14 Jan '14

Dear all, I need to build a debug version of mserver5 in order to attach gdb to it to debug my bulk version of a UDF. How exactly do I go about this? It seems there is no debug target in the standard Makefile. Best regards, Alastair

2 1

Using msqldump while data is being inserted.
by Pedro Salgueiro 13 Jan '14

13 Jan '14

Hi, What is the expected behavior of msqldump if I try to a backup a database or table, and at the middle of the backup process new data is inserted? Would the dump process ignore the new data, or it would rollback and restart all over again? Thank you, Pedro Salgueiro

2 3

couldn't locate mserver5
by Hanumanth Maduri 10 Jan '14

10 Jan '14

Hello All, I have compiled the source of monetdb ~/Downloads/MonetDB-11.15.17$ monetdb -v MonetDB Database Server Toolkit v1.1 (Feb2013-SP5) All the following things were installed in /usr/local/bin malsample.pl mclient Mfilter.py MkillUsers monetdb Mtest.py sample0 sample2 sample4 smack01 sqlsample.pl tomograph Mapprove.py Mdiff Mfilter.pyc Mlog msqldump Mtimeout sample1 sample3 smack00 sqlsample.php stethoscope There is nothing like mserver5 nor monetdbd. Please let me know if there is any problem with my build. Thanks, -Hanu

3 3

Fwd: Bulk-loading in monetdb with primary key constraint
by Vasilis Efthymiou 23 Dec '13

23 Dec '13

I am trying to bulk-load a list of objects into a one-column (primary key) db. The only reason is to remove duplicates. I can't load the list in memory, because the file size is way greater than my memory size. I use monetdb's COPY-INTO<http://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto>command, but I don't want it to fail when there is a duplicate. I want it to add everything that is not a duplicate and skip the duplicates. Is there any way to do that with monetdb? Any other way? Thank you in advance!

3 3

varchar split function
by imad hajj chahine 17 Dec '13

17 Dec '13

Hi, I am trying to implement a function that split a varchar and returns a table with the splitted values. this is what i have so far but i dont know how to complete the implementation. CREATE FUNCTION EV_SPLIT(stringtosplit varchar(255), chartospliton char) RETURNS TABLE (id int, val varchar(255), hashval BIGINT) BEGIN --if(EV_TABLEEXISTS('tmp_evsplitres') = 0) --then -- create local temporary table tmp_evsplitres (id int, val varchar(255), hashval BIGINT); --end if; declare idx int, tmpVal varchar(255); set idx = 0; --start transaction; while (position(chartospliton in str)>0) do set tmpVal = substring(str, 0, position(',' in str)-1); insert into tmp_evsplitres values(idx, tmpVal, hash(tmpVal)); set str = substring(str, position(',' in str), length(str)); set idx = idx + 1; end while; insert into tmp_evsplitres values(idx, str, hash(str)); RETURN TABLE ( select * from tmp_evsplitres ); --commit; END;

2 1

Inline function much faster than UDF in C?
by Alastair McKinley 13 Dec '13

13 Dec '13

Hi everyone, I have only been using MonetDB for a few weeks now and I have come across a very unexpected result when using User Defined Functions in C. I have a query which does essentially this: TABLE small_table ( int col1; float p; ); TABLE big_table ( int col1; int col2; float p; ); SELECT col1,1+SUM(MY_FUNCTION(big.p,small.p)) FROM small_table small inner join big_table big on big.col2 == small.col1 group by big.col1; My function is f(x,y) = 0.7213*(x*ln(x/x+y) + y*ln(y/x+y)) I wrote an optimised version of this function using sse intrinsics in C and compiled it into MonetDB. To my surprise, I discovered that writing this function inline in the query like so: 1+SUM(0.7213475*(big.p*LOG(big.p/(big.p+small.p))+small.p*LOG(small.p/(big.p+small.p)))) is a lot faster! The query takes approx 2 seconds with the C version and less than 0.5 seconds with the inline version in SQL! Can anyone explain this strange result? Best regards, Alastair

4 12

BUG 3259 should be re-opened
by Roberto Cornacchia 12 Dec '13

12 Dec '13

Hello, I cannot re-open bug 3259, but I believe t should be. After the bug had been fixed by Niels, it seems to have been re-introduced. The changeset below (after the fix) approves a MAL plan that in fact is the wrong one the original report refers to: the join has again been replaced by a CROSS-PRODUCT followed by inequality selections. Changeset d0b65a3e8eb6<http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d0b65a3e8eb6>, made by Stefan Manegold <Stefan.Manegold(a)cwi.nl> in the MonetDB repo, refers to this bug. For complete details, see http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d0b65a3e8eb6 Changeset description: between.Bug-3259: approved new MAL plan after changeset 79e66f677113 <http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=79e66f677113>

1 1

create a database
by Gaetano Mele 12 Dec '13

12 Dec '13

Hello can you tell me how do i create a new database with monetdb? Thanks

4 7

Dump database with user != monetdb
by Klaus Kruse 10 Dec '13

10 Dec '13

Hello, I posted my issues with dumping a database from MonetDB a while ago. The issue is still ongoing. It reduces to the fact, that no other user than the default monetdb-user has enough rights to dump a database. I played around with the various tables regarding users and schemas (sys.auths , sys.user_role ) but it's still failing on 'sys.db_user_info' : msqldump -u dump test password: -- msqldump dump database Tue Dec 10 17:19:43 2013 -- MonetDB v11.15.7 (Feb2013-SP2), 'mapi:monetdb://dev01.picalike.corpex-kunden.de:50000/test' START TRANSACTION; MAPI = (dump) /tmp/.s.monetdb.50000 QUERY = SELECT "name" FROM "sys"."auths" WHERE "name" NOT IN (SELECT "name" FROM "sys"."db_user_info") AND "grantor" <> 0 ORDER BY "name" ERROR = !SELECT: access denied for dump to table 'sys.db_user_info' So, my question to all subscribers: Does anybody managed to create another user with full rights to dump the database and when so, how? Any advice is greatly apprecitated. Best regards Klaus Kruse -- Corpex Internet GmbH * Schauenburgerstrasse 6 * D-20095 Hamburg Tel: +49 40 822268-0 * Fax: +49 40 822268-100 * http://www.corpex.de/ HRB 78752, Amtsgericht Hamburg, Sitz: Hamburg, USt.-Id: DE 213001412 Geschäftsführer: Daniel Hanelt

1 0

Moving monetdb data to another directory
by Rémi Charpentier 10 Dec '13

10 Dec '13

I am trying to change the default data directory of MonetDB. I installed monetdb using the ubuntu package, by default the data is stored in: /var/lib/monetdb I am running out of space, and I like to migrate the data to another folder. Does anyone know how to do that? I would welcome a solution that doesn't involve compiling monetdb from sources... Thanks in advance Remi

2 2

Listing database objects in schema
by Lee Hachadoorian 06 Dec '13

06 Dec '13

Is there a way to list tables in a particular schema or even in all schemas? \d and \dt seem to only return user tables in the sys schema. Also, what does \ds return? I have \dS system tables \dt user tables \dv views \ds ????? \df functions \dn schemas Best, --Lee -- Lee Hachadoorian Asst Professor of Geography, Dartmouth College http://geospatial.commons.gc.cuny.edu/<http://freecity.commons.gc.cuny.edu/>

2 2

Bulk loading file with non-UTF8 characters
by Lee Hachadoorian 06 Dec '13

06 Dec '13

I need to bulk load some files with characters like ñ and é. I would do this using the LATIN1 encoding. I read that bulk loading from file assumes UTF8, but bulk loading from STDIN will use encoding of mclient. I would like to know: (a) How do I determine and change the client encoding? (b) What is an example for piping STDIN to mclient? Regarding the latter, I tried: cat /my/file.csv | mclient -d mydb -u monetdb -s "COPY INTO mytable FROM (STDIN) USING DELIMITERS ',', '\n' , '"' NULL AS '';" I wasn't sure how the embedded " would be handled, but the terminal basically became unresponsive until I ctl-C'ed out of it. Note the the file was similar in size to several others (without non-UTF8 characters) that loaded in seconds within mclient. Best, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://geospatial.commons.gc.cuny.edu http://freecity.commons.gc.cuny.edu

3 4

Load file MonetDB Client
by Gaetano Mele 06 Dec '13

06 Dec '13

Hello i'm an italian student. I am loading a file .sql with MonetDB Client. But, during the file's loading, happens that the Client suddenly stops working. Why does this happen? Thanks

3 6

Strange behavior: dumping results from SELECT query (or inserting it into a table) does not contain the proper data until table is dumped/reloaded
by Bas Kaptijn 06 Dec '13

06 Dec '13

Hi, I can reproduce a MonetDB 2013 - SP5 database which gets in such a state the following first query does not return a result with a record containing '20130510199500000099' as value for the field "ida" whereas the second query (with only a little extra at the end) does while clearly this should never be the case. Moreover: the problem disappears when I dump, drop and reload the table analysis_04_1 ! testdpe99.sql : SELECT A.identificatierecord AS idA, B.identificatierecord AS idB, A.identificatierecord||'-'||B.identificatierecord AS ctxid, 'D.P.E.99.99' AS testcode FROM analysis_04_1 AS A INNER JOIN analysis_04_1 AS B ON A.instellingscode = B.instellingscode AND A.verzekerdennummer = B.verzekerdennummer WHERE NOT A.identificatierecord = B.identificatierecord AND (B.Zorgtypecode = '11' OR B.Zorgtypecode = '21') AND ( (A.BegindatumPrestatie <= B.EinddatumPrestatie AND A.EinddatumPrestatie >= B.BegindatumPrestatie AND NOT ( A.EinddatumPrestatie > A.BegindatumPrestatie AND B.EinddatumPrestatie > B.BegindatumPrestatie AND ( A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie ) )) OR (A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie OR A.BegindatumPrestatie=(B.EinddatumPrestatie + INTERVAL '1' day) OR B.BegindatumPrestatie=(A.EinddatumPrestatie + INTERVAL '1' day) ) ); testdpe99-2.sql : SELECT A.identificatierecord AS idA, B.identificatierecord AS idB, A.identificatierecord||'-'||B.identificatierecord AS ctxid, 'D.P.E.99.99' AS testcode FROM analysis_04_1 AS A INNER JOIN analysis_04_1 AS B ON A.instellingscode = B.instellingscode AND A.verzekerdennummer = B.verzekerdennummer WHERE NOT A.identificatierecord = B.identificatierecord AND (B.Zorgtypecode = '11' OR B.Zorgtypecode = '21') AND ( (A.BegindatumPrestatie <= B.EinddatumPrestatie AND A.EinddatumPrestatie >= B.BegindatumPrestatie AND NOT ( A.EinddatumPrestatie > A.BegindatumPrestatie AND B.EinddatumPrestatie > B.BegindatumPrestatie AND ( A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie ) )) OR (A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie OR A.BegindatumPrestatie=(B.EinddatumPrestatie + INTERVAL '1' day) OR B.BegindatumPrestatie=(A.EinddatumPrestatie + INTERVAL '1' day) ) ) AND A.identificatierecord = '20130510199500000099'; The table analysis_04_1 is the result of a SELECT query after which it is altered and updated quite some times adding extra fields etc. No errors seem to be reported in that process. Before reloading the table analysis_04_1 I get: bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99-2.sql | grep 20130510199500000099 | 20130510199500000099 | 20130313587700000003 | 20130510199500000099-20130313587700000003 | D.P.E.99.99 | bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99.sql | grep 20130510199500000099 bkaptijn@DevRef1:~/DDDB$ But after reloading the table (using in mclient: \> dumpfile, \D analysis_04_1, DROP TABLE analysis_04_1;, \< dumpfile): bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99.txt | grep 20130510199500000099 | 20130510199500000099 | 20130313587700000003 | 20130510199500000099-20130313587700000003 | D.P.E.99.99 | bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99-2.txt | grep 20130510199500000099 | 20130510199500000099 | 20130313587700000003 | 20130510199500000099-20130313587700000003 | D.P.E.99.99 | bkaptijn@DevRef1:~/DDDB$ Is there a known issue which does play a role here or what can I do to investigate this further / give more information? Sincerely, Bas Kaptijn

1 0