November 2018 - users-list

Adding columns to tables via binary bulk loading
by Anderson, David B 12 Nov '18

12 Nov '18

All, Is it possible to add a column to an existing table via binary bulk loading? We have a table with ~1 billion rows and we would like to add columns to the table, but would prefer bulk loads for speed. Also, does anyone have a snippet of code to create the binary file format that bulk loading expects. I've tried a couple versions (simple int and double), but the server wouldn't recognize the files. Thanks, Dave

2 1

Unable to get sys.queue for all the users
by Murthy, Gautham 07 Nov '18

07 Nov '18

Hello, We are unable to get the information for all active users sessions when we use sys.queue as monetdb user. Also, the tried as user other than monetdb, it returned no access, We had provided the user select privilege on the sys.queue view. [monetadmin@lnx1487 scripts]$ /monet_binaries/MonetDB-11.27.13_PY/bin/mclient mapi:monetdb://lnx1487:50053/DB_BWS_S Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP4) Database: MonetDB v11.27.13 (Jul2017-SP4), 'mapi:monetdb://lnx1487:50053/DB_BWS_S' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select "user","active",count(*) as "No. Of Connections" from sys.sessions() group by "user","active"; +-------------------+--------+--------------------+ | user | active | No. Of Connections | +===================+========+====================+ | DM_POS_BWS_STG_P6 | true | 1 | | DM_POS_BWS_STG_P6 | false | 3 | | monetdb | true | 1 | +-------------------+--------+--------------------+ 3 tuples (6.027ms) sql>select * from sys.queue; +------+---------+----------------------------+----------------------------+----------+---------+--------+--------------------------+ | qtag | user | started | estimate | progress | status | tag | query | +======+=========+============================+============================+==========+=========+========+==========================+ | 3630 | monetdb | 2018-11-07 17:07:34.000000 | null | null | running | 3630@0 | select * from sys.queue; | +------+---------+----------------------------+----------------------------+----------+---------+--------+--------------------------+ 1 tuple (1.610ms) sql>\q [monetadmin@lnx1487 scripts]$ Thank You, Gautham

4 3

Cannot setup monetdb on Centos 7
by GAUQUELIN, Philippe 05 Nov '18

05 Nov '18

Hi Need help on setup. Thanks for your answer Trying to install I got this message. The GPG keys listed for the "MonetDB 7 - x86_64" repository are already installed but they are not correct for this package. More details below: []# yum reinstall https://dev.monetdb.org/downloads/epel/MonetDB-release-epel.noarch.rpm Loaded plugins: fastestmirror, langpacks MonetDB-release-epel.noarch.rpm | 6.4 kB 00:00:00 Examining /var/tmp/yum-root-x4i3eb/MonetDB-release-epel.noarch.rpm: MonetDB-release-epel-1.3-1.monetdb.noarch Resolving Dependencies --> Running transaction check ---> Package MonetDB-release-epel.noarch 0:1.3-1.monetdb will be reinstalled --> Finished Dependency Resolution Dependencies Resolved ======================================================================================================================================================================= Package Arch Version Repository Size ======================================================================================================================================================================= Reinstalling: MonetDB-release-epel noarch 1.3-1.monetdb /MonetDB-release-epel.noarch 4.0 k Transaction Summary ======================================================================================================================================================================= Reinstall 1 Package Total size: 4.0 k Installed size: 4.0 k Is this ok [y/d/N]: y Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : MonetDB-release-epel-1.3-1.monetdb.noarch 1/1 Verifying : MonetDB-release-epel-1.3-1.monetdb.noarch 1/1 Installed: MonetDB-release-epel.noarch 0:1.3-1.monetdb Complete! [root@franalytic02 tmp]# []# rpm --import https://www.monetdb.org/downloads/MonetDB-GPG-KEY []# yum install MonetDB-SQL-server5 MonetDB-client Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile * base: centos.crazyfrogs.org * epel: mirrors.ircam.fr * extras: centos.crazyfrogs.org * updates: centos.mirror.fr.planethoster.net Resolving Dependencies --> Running transaction check ---> Package MonetDB-SQL-server5.x86_64 0:11.31.11-20181011.el7 will be installed --> Processing Dependency: MonetDB5-server(x86-64) = 11.31.11-20181011.el7 for package: MonetDB-SQL-server5-11.31.11-20181011.el7.x86_64 --> Processing Dependency: libstream.so.11()(64bit) for package: MonetDB-SQL-server5-11.31.11-20181011.el7.x86_64 --> Processing Dependency: libmonetdb5.so.25()(64bit) for package: MonetDB-SQL-server5-11.31.11-20181011.el7.x86_64 --> Processing Dependency: libbat.so.17()(64bit) for package: MonetDB-SQL-server5-11.31.11-20181011.el7.x86_64 ---> Package MonetDB-client.x86_64 0:11.31.11-20181011.el7 will be installed --> Running transaction check ---> Package MonetDB.x86_64 0:11.31.11-20181011.el7 will be installed ---> Package MonetDB-stream.x86_64 0:11.31.11-20181011.el7 will be installed ---> Package MonetDB5-server.x86_64 0:11.31.11-20181011.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ======================================================================================================================================================================= Package Arch Version Repository Size ======================================================================================================================================================================= Installing: MonetDB-SQL-server5 x86_64 11.31.11-20181011.el7 monetdb 854 k MonetDB-client x86_64 11.31.11-20181011.el7 monetdb 275 k Installing for dependencies: MonetDB x86_64 11.31.11-20181011.el7 monetdb 1.0 M MonetDB-stream x86_64 11.31.11-20181011.el7 monetdb 197 k MonetDB5-server x86_64 11.31.11-20181011.el7 monetdb 853 k Transaction Summary ======================================================================================================================================================================= Install 2 Packages (+3 Dependent packages) Total size: 3.1 M Installed size: 9.1 M Is this ok [y/d/N]: y Downloading packages: warning: /var/cache/yum/x86_64/7/monetdb/packages/MonetDB-stream-11.31.11-20181011.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID df0e54f3: NOKEY Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-MonetDB Importing GPG key 0x0583366F: Userid : "MonetDB Database System Packager <monet(a)cwi.nl>" Fingerprint: 213e 64dc d5dd c2c0 63cc 39fd 053c 3ed4 0583 366f Package : MonetDB-release-epel-1.3-1.monetdb.noarch (installed) >From : /etc/pki/rpm-gpg/RPM-GPG-KEY-MonetDB Is this ok [y/N]: y Importing GPG key 0x0583366F: Userid : "MonetDB Database System Packager <monet(a)cwi.nl>" Fingerprint: 213e 64dc d5dd c2c0 63cc 39fd 053c 3ed4 0583 366f Package : MonetDB-release-epel-1.3-1.monetdb.noarch (installed) >From : /etc/pki/rpm-gpg/RPM-GPG-KEY-MonetDB Is this ok [y/N]: y Public key for MonetDB-stream-11.31.11-20181011.el7.x86_64.rpm is not installed Failing package is: MonetDB-stream-11.31.11-20181011.el7.x86_64 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-MonetDB Philippe

2 1

Using capi.eval from MAL
by Renato Marroquín Mogrovejo 04 Nov '18

04 Nov '18

Hi all, I am trying to use the following patten from MAL[1] pattern eval(fptr:ptr,cpp:bit,expr:str,arg:any...):any... However, I am not sure how I should create the first parameter <fptr:ptr> from MAL. I am just trying to be able to call a C-UDF from MAL. From capi.c, it would seem that it is the client's context but I don't know how to get a handle of that. Any pointers are highly appreciated. Thanks! Renato M. [1] https://github.com/MonetDB/MonetDB/blob/2310e49a349ebcb8ff5b5c585928c8f4bd1…

2 4

Failed to import table 'tier3_score_201807_part53', Failed to extend the BAT, perhaps disk full
by Murthy, Gautham 04 Nov '18

04 Nov '18

Hello, We are hitting the below error while loading data into table, There is no space crunch or memory (resource crunch). Please let us know if there are any restrictions in loading data. sql>COPY INTO "DM_POS_PROSCORES_MRT_P1".tier3_score_201807_part53 FROM '/backup8/schema_backups/tier3_score_201807_part53'; Failed to import table 'tier3_score_201807_part53', Failed to extend the BAT, perhaps disk full [monetadmin@lnx1538 monet_data01]$ /monet_binaries/MonetDB-11.27.13_PY/bin/monetdbd version MonetDB Database Server v1.7 (Jul2017-SP4) You have mail in /var/spool/mail/monetadmin [monetadmin@lnx1538 monet_data01]$ /monet_binaries/MonetDB-11.27.13_PY/bin/monetdb version MonetDB Database Server Toolkit v1.1 (Jul2017-SP4) [monetadmin@lnx1538 monet_data01]$ mclient -version mclient, the MonetDB interactive terminal (Jul2017-SP4) support for command-line editing compiled-in character encoding: UTF-8 [monetadmin@lnx1538 monet_data01]$ df -hP /monet_data01 Filesystem Size Used Avail Use% Mounted on /dev/mapper/encrmonet_data01lv 12T 7.0T 4.4T 62% /monet_data01 [monetadmin@lnx1538 monet_data01]$ ps -ef|grep -i monetdbd monetad+ 331928 1 0 Nov02 ? 00:00:14 /monet_binaries/MonetDB-11.27.13_PY/bin/monetdbd start /monet_data01/DBFARM_TEST01 monetad+ 333365 332892 0 10:57 pts/9 00:00:00 grep --color=auto -i monetdbd monetad+ 405353 1 0 Oct31 ? 00:00:30 /monet_binaries/MonetDB-11.27.13_PY/bin/monetdbd start /monet_data01/DBFARM_PROSCORE_P [monetadmin@lnx1538 monet_data01]$ [monetadmin@lnx1538 monet_data01]$ free -g total used free shared buff/cache available Mem: 1007 18 302 7 685 978 Swap: 31 0 31 [monetadmin@lnx1538 monet_data01]$ Thank You, Gautham

3 3

Append an array of values to a BUN
by Anton Kravchenko 02 Nov '18

02 Nov '18

Hi there, Is there a way to append an array of values to a BUN, instead one value at a time? sql_table *tbl = mvc_bind_table(m, sch, "atable"); sql_column *col = mvc_bind_column(m, tbl, "acolumn"); BAT *tmp1 = COLnew(0, TYPE_lng, (BUN)nrows, TRANSIENT); long long v_long[3] = {100000000009,100000000010,100000000011}; for (irow=0; irow<nrows; irow++) BUNappend(tmp1, &v_long[irow], FALSE); mvc_append_column(m->session->tr, col1, tmp1); mvc_commit(m, 0, tr->name); bat_destroy(tmp1); Thanks, Anton

2 5