- users-list - monetdb.org

COPY subquery INTO file_name - INCLUDE field name
by Ariel Abadi 10 Nov '16

10 Nov '16

Hi! We are using the COPY *subquery* INTO *file_name *[ [USING] DELIMITERS *field_separator* [',' *record_separator* [ ',' *string_quot*e ]]] [ NULL AS *null_string* ] BUT we need to have in the file the name of the fields on the first row. Im looking on the documentation, but I cannot find anything related to this, can someone help me with this? Thks in advance!

3 3

Fwd: Performance tuning
by Rajendra Bhat 09 Nov '16

09 Nov '16

Hi team, I am running monetdb on 16 core processor and 30gb ram. I have table around 54 fields. gzip compressed csv size is around 6.5 GB. query will take around 6 min. please suggest tuning options. query: Select count(distinct userid),sum(mrp)/sum(noOfItems),sum(mrp)/sum( orderCount),sum(revenue),sum(orderCount),sumnoOfItems),sum( returnCount),sum(rtoCount),sum(0),sum(0),sum(tDiscountPer),sum(cDiscountPer) from historical where gender='men' and brand='puma' Thanks and Regards Rajendra Bhat -- Thanks and Regards Rajendra Bhat

2 3

How do I force start a new monetdbd instance?
by Anderson, David B 09 Nov '16

09 Nov '16

Or what files do I need to delete from the file system? When I try to start a new monetdbd instance, I am getting the error: another monetdbd is already running but there are no monetdb processes running. . .

2 1

TimeZone conversion functions
by Ariel Abadi 03 Nov '16

03 Nov '16

Hi! Can anyone help me understand how or where I can check the included functions on MonetDB to convert timestamp from one timezone to another? Thanks in advance! Rgds Ariel

2 2

MAL optimizer result inspection
by Moritz Bruder 02 Nov '16

02 Nov '16

Hi, is it possible to look at the output of a specific optimizer? I tried using *optimizer.showFlowGraph*, which just hangs, and *optimizer.showPlan*, which does not show some variables. Is there maybe something I missed? Regards, Moritz

2 1

Getting "NOT NULL constraint violated" for a column with a DEFAULT value
by Eyal Rozenberg 24 Oct '16

24 Oct '16

I have a column defined as follows: "div5tailnum" VARCHAR(10) NOT NULL DEFAULT '' (that's the output of \d mytable), or if you like: Div5TailNum VARCHAR(10) NOT NULL DEFAULT '' (that's from the CREATE TABLE command). I'm trying to load data from a CSV file using the following SQL: COPY OFFSET 2 INTO mytable FROM file.csv USING DELIMITERS ',','\n','"' NULL AS ''; but I get: INSERT INTO: NOT NULL constraint violated for column ontime.div5tailnum shouldn't that be impossible, seeing how I've provided a default?

3 4

Cannot install MonetDB on Ubuntu 16.04.1 LTS (Xenial Xerus)
by Peter Lee 24 Oct '16

24 Oct '16

Hi- I couldn't install MonetDB on Ubuntu 16.04.1 LTS (Xenial Xerus) hosted on EC2 yesterday. I kept getting the error below. It looks like the repository does not have a "source" folder. However, I noticed that the "Trusty" version had a "source" folder, and so I tried installing the Trusty version on my Xenial box, and it installed successfully. So far, I haven't encountered any problems, but was there anything I did wrong in the installation process? Will I encounter problems down the road with the Trusty version installed on a Xenial box? $ sudo apt-get update Hit:1 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial InRelease Hit:2 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-updates InRelease Hit:3 http://us-east-1.ec2.archive.ubuntu.com/ubuntu xenial-backports InRelease Get:4 http://security.ubuntu.com/ubuntu xenial-security InRelease [94.5 kB] Ign:5 http://dev.monetdb.org/downloads/deb xenial InRelease Get:6 http://dev.monetdb.org/downloads/deb xenial Release [2,715 B] Get:7 http://dev.monetdb.org/downloads/deb xenial Release.gpg [163 B] Fetched 97.3 kB in 0s (185 kB/s) Reading package lists... Done W: http://dev.monetdb.org/downloads/deb/dists/xenial/Release.gpg: Signature by key 213E64DCD5DDC2C063CC39FD053C3ED40583366F uses weak digest algorithm (SHA1) E: Failed to fetch http://dev.monetdb.org/downloads/deb/dists/xenial/Release Unable to find expected entry 'monetdb/source/Sources' in Release file (Wrong sources.list entry or malformed file) E: Some index files failed to download. They have been ignored, or old ones used instead.

3 2

Fwd: query with group.subgroupdone' undefined in
by José Cavieres 22 Oct '16

22 Oct '16

Hello, Can anyone explain why adding "2*" in the following query results in an error: Original Query: sql>select (CAST(now()-'1970-01-01 00:00:00' as BIGINT)) as minuto,avg(CAST(N6_end - trStart as BIGINT)) as promedio from testquery where trStart>'2016-10-06 00:00:00.000000' GROUP BY minuto; +---------------+--------------------------+ | minuto | promedio | +===============+==========================+ | 1475770734000 | null | +---------------+--------------------------+ Query with "2*": sql>select 2*CAST(now()-'1970-01-01 00:00:00' as BIGINT) as minuto,avg(CAST(N6_end - trStart as BIGINT)) as promedio from testquery where trStart>'2016-10-06 00:00:00.000000' GROUP BY minuto; TypeException:user.s20_1[10]:'group.subgroupdone' undefined in: (X_15:any,r1_15:any,r2_15:any) := group.subgroupdone(X_14:hge); program contains errors Explain: ------------------------------------------------------------ ------------------------------------------------------------ ---------------------+ | mal | +=========================================================== ============================================================ ======================+ | function user.s22_1(A0:bte,A1:str,A2:str):void; | sql.mvc(); | | X_5:timestamp := mtime.current_timestamp(); | | A1; | | calc.timestamp(X_6,7,1); | | X_10:lng := mtime.diff(X_5,X_7); | | calc.lng(X_10); | | calc.hge(X_11); | | A0; | | X_14:hge := calc.*(X_12,X_13); | | (X_15,r1_15,r2_15) := group.subgroupdone(X_14); | | algebra.projection(r1_15,X_14); | | bat.setKey(X_18,true); | | X_21:bat[:oid] := sql.tid(X_4,"sys","testquery") ; | | X_24:bat[:timestamp] := sql.bind(X_4,"sys","testquery" ,"trstart",0); | | (X_27,r1_27) := sql.bind(X_4,"sys","testquery" ,"trstart",2); | | X_30:bat[:timestamp] := sql.bind(X_4,"sys","testquery" ,"trstart",1); | | sql.delta(X_24,X_27,r1_27,X_30); | | algebra.projection(X_21,X_31); | | X_33:bat[:timestamp] := mal.multiplex("calc","timestam p",X_32,7); | | A2; | | calc.timestamp(X_36,7); | | algebra.thetasubselect(X_33,X_37,">"); | | X_40:bat[:timestamp] := sql.bind(X_4,"sys","testquery" ,"n6_end",0); | | (X_42,r1_42) := sql.bind(X_4,"sys","testquery" ,"n6_end",2); | | X_44:bat[:timestamp] := sql.bind(X_4,"sys","testquery" ,"n6_end",1); | | sql.delta(X_40,X_42,r1_42,X_44); | | algebra.projection(X_21,X_45); | | algebra.projection(X_38,X_46); | | algebra.projection(X_38,X_32); | | X_49:bat[:lng] := mal.multiplex("mtime","diff",X _47,X_48); | | batcalc.lng(X_49); | | batcalc.dbl(X_52); | | X_54:bat[:dbl] := aggr.subavg(X_53,X_15,r1_15,tr ue,true); | | bat.new(nil:oid,nil:str); | | bat.new(nil:oid,nil:str); | | bat.new(nil:oid,nil:str); | | bat.new(nil:oid,nil:int); | | bat.new(nil:oid,nil:int); | | bat.append(X_58,".L1"); | | bat.append(X_61,"minuto"); | | bat.append(X_62,"hugeint"); | | bat.append(X_63,72); | | bat.append(X_65,0); | | bat.append(X_66,"sys.L3"); | | bat.append(X_68,"promedio"); | | bat.append(X_70,"double"); | | bat.append(X_72,53); | | bat.append(X_74,0); | | sql.resultSet(X_76,X_78,X_80,X_82,X_84,X_19,X_54); | | end user.s22_1; | | # querylog.define("explain select 2*cast(now()-\\'1970-01-01 00:00:00\\' as bigint) as minuto,avg(cast(n6_end - trstart as bigint)) as prom | : edio from testquery where trstart>\\'2016-10-06 00:00:00.000000\\' group by minuto;","default_pipe") : Environment: - MonetDB v11.23.7 (Jun2016-SP1) from release package - Centos and Ubuntu - Create table attached - The type of the columns in the query is timestamp - A previous versions of this email was rejected because the "create table" statement is too big for the 40Kbytes limit of this list. Thanks Jose Cavieres

4 7

system init script
by Heyns Emiliano 21 Oct '16

21 Oct '16

Does anyone have a working init script for MonetDB? -- Groet, Emiliano Heyns

1 0

how well does MonetDB scale to large servers (e.g. 4 CPU/64+ cores/2+TB RAM)?
by Igor Mendelev 21 Oct '16

21 Oct '16

[Sorry if this question was already asked and answered before - I'm new to this mailing list.] Recently servers with up to 6TB RAM became relatively affordable to be used with MonetDB (considering total cost of server hw + DB sw license) - but will MonetDB scale well to use all that hardware? I know it can be tried on the cloud using (for example) x1.32xlarge EC2 spot instances but before doing that I'd like to ask people with more knowledge/experience with MonetDB if it at least looks feasible, if there are any important tuning parameters for such hw etc. If you've tired similar hardware and/or know some relevant implementaton details maybe you can share your experience? Thanks. Igor

2 1