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,
NikolaHi NikolaIm 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_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-listOn 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
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list