Hi Nikola Im not sure if it is a typo on your question or on your process, but you wrote the following in the first SELECT.. "sql> SELECT day, count(*) FROM "table" GROUP BY day;" You are running your query against the table "table" instead of the table "trades". Do you think that would be the problem? Rgds *Ariel Abadi* Direct: +54 11 5279.2054 Mobile:+54 9 11 6050.0101 Email: aabadi@starconnecting.com Web: www.starconnecting.com On Tue, May 3, 2016 at 5:56 AM, Knezevic Nikola <nikkne@gmx.ch> wrote:
Hi all,
I'm splitting my data into per-day tables and importing them into monetdb. Before the import, I first create a per-day table and then use 'ALTER TABLE "table" ADD TABLE "table__2016-05-03";' command (passed to cursor.execute() in python. "table" is defined to be a MERGE TABLE.
Now, I had only a single day that was populated on the initial run, when both MERGE TABLE and day table were created. Today, I added a new day table and ran this:
sql> SELECT day, count(*) FROM "table" GROUP BY day; +------------+---------+ | day | L1 | +============+=========+ | 2016-04-26 | 2959601 | +------------+---------+ sql>select day, count(*) from "trades__2016-05-02" group by day; +------------+---------+ | day | L1 | +============+=========+ | 2016-05-02 | 2220143 | +------------+---------+
So, the output is missing the current day, and I thought my python code didn't properly tie these tables together. However, inspecting sys.* tables gives me this:
sql>\d (snip...) MERGE TABLE my_namespace.trades TABLE my_namespace.trades__2016-04-26 TABLE my_namespace.trades__2016-05-02 (snip...) sql>select * from sys.tables where name like 'trades%';
+-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ | id | name | schema_id | query | type | system | commit_action | access | temporary |
+=======+====================+===========+=======+======+========+===============+========+===========+ | 13480 | trades | 6497 | null | 3 | false | 0 | 0 | 0 | | 13518 | trades__2016-04-26 | 6497 | null | 0 | false | 0 | 0 | 0 | | 16203 | trades__2016-05-02 | 6497 | null | 0 | false | 0 | 0 | 0 |
+-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ 3 tuples (3.323ms) sql>select * from sys.dependencies where depend_id=13480; +-------+-----------+-------------+ | id | depend_id | depend_type | +=======+===========+=============+ | 13518 | 13480 | 2 | | 16203 | 13480 | 2 | +-------+-----------+-------------+
Finally, if I manually add the table, it succeeds:
sql>alter table "trades" add table "trades__2016-05-02"; operation successful (13.894ms) sql>select day, count(*) from "trades" group by day; +------------+---------+ | day | L1 | +============+=========+ | 2016-04-26 | 2959601 | | 2016-05-02 | 2220143 | +------------+---------+ 2 tuples (44.196ms) sql>select * from sys.tables where name like 'trades%';
+-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ | id | name | schema_id | query | type | system | commit_action | access | temporary |
+=======+====================+===========+=======+======+========+===============+========+===========+ | 13480 | trades | 6497 | null | 3 | false | 0 | 0 | 0 | | 13518 | trades__2016-04-26 | 6497 | null | 0 | false | 0 | 0 | 0 | | 16203 | trades__2016-05-02 | 6497 | null | 0 | false | 0 | 0 | 0 |
+-------+--------------------+-----------+-------+------+--------+---------------+--------+-----------+ 3 tuples (7.445ms) sql>select * from sys.dependencies where depend_id=13480; +-------+-----------+-------------+ | id | depend_id | depend_type | +=======+===========+=============+ | 13518 | 13480 | 2 | | 16203 | 13480 | 2 | +-------+-----------+-------------+ 2 tuples (1.714ms)
For completeness, python code looks like this (cleaned up, to present the case, keep in mind that on initial import, this worked):
if args.create: if args.partition and has_day: dest_create_merge_table = re.sub('CREATE TABLE', 'CREATE MERGE TABLE', dest_create_table, count=1) print("Creating table with the following content:\n{ddl}".format(ddl=dest_create_merge_table), file=sys.stderr) try: cursor = monet_conn.cursor() cursor.execute(dest_create_merge_table) except monetdb.exceptions.OperationalError as e: if re.search('CREATE TABLE: name .* already in use', e.args[0]): print("Master table already present") else: raise(e) finally: cursor.close()
if args.partition and has_day: cursor = monet_conn.cursor() for c in conditions: (text, day, sth) = c dest_create_partition = re.sub('CREATE TABLE "{table}"'.format(table=table), 'CREATE TABLE "{partition}"'.format(partition=partition_name(table, day)), dest_create_table, count=1)
print("Creating partition with the following content:\n{ddl}".format(ddl=dest_create_partition), file=sys.stderr) cursor.execute(dest_create_partition) if args.debug: print('ALTER TABLE "{table}" ADD TABLE "{partition}"'.format(table=table, partition=partition_name(table, day))) cursor.execute('ALTER TABLE "{table}" ADD TABLE "{partition}"; COMMIT;'.format(table=table, partition=partition_name(table, day))) cursor.close()
Can someone please help me debug this issue? Is this a bug? IIUC, metadata points that "trades__2016-05-02" belongs to "trades", but the queries do not reflect that.
Thanks, Nikola _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list