Problem: 'ALTER TABLE x ADD TABLE y' does not merge the data in from y.
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
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 followingcontent:\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
Hi Nikola,
If you remote the table from trades run the code that is meant to add the table to your MERGE table trades what does the below query show just one table in the plan ?
plan select * from trades;
Regards,
Brian Hood
On Tue, May 3, 2016 at 10:42 AM, Knezevic Nikola nikkne@gmx.ch wrote:
Hi Abadi,
sorry, it was a typo, as I was piecing things together for a coherent email. The actual query is the correct one: SELECT day, count(*) FROM "trades" GROUP BY day; and yields the incorrect (already reported) answer.
There is no table "table" in the system.
Thanks, Nikola
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 followingcontent:\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
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
if your DROP the table trades merge table i meant*
On Tue, May 3, 2016 at 12:53 PM, Brian Hood brianh6854@googlemail.com wrote:
Hi Nikola,
If you remote the table from trades run the code that is meant to add the table to your MERGE table trades what does the below query show just one table in the plan ?
plan select * from trades;
Regards,
Brian Hood
On Tue, May 3, 2016 at 10:42 AM, Knezevic Nikola nikkne@gmx.ch wrote:
Hi Abadi,
sorry, it was a typo, as I was piecing things together for a coherent email. The actual query is the correct one: SELECT day, count(*) FROM "trades" GROUP BY day; and yields the incorrect (already reported) answer.
There is no table "table" in the system.
Thanks, Nikola
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 followingcontent:\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
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (3)
-
Ariel Abadi -
Brian Hood -
Knezevic Nikola