I have a table with data similar to the following:
gs[public]> gs[public]> tql t1 select *;
9 results. (11 ms)
gs[public]> get
id,serial,intime
1,137192745719237,2022-11-11T05:33:15.979Z
2,137192745719246,2022-11-11T05:34:16.271Z
3,237192745719237,2022-11-11T05:34:21.189Z
5,337192745719237,2022-11-11T05:35:30.048Z
6,137192745719255,2022-11-11T05:35:38.121Z
7,137192745719279,2022-11-11T05:35:41.322Z
8,137192745719210,2022-11-11T05:35:47.521Z
9,137192745719201,2022-11-11T05:35:50.586Z
10,137192745719205,2022-11-11T05:35:53.671Z
The 9 results had been acquired.
gs[public]>
which currently has more than 30 million rows. Some data query statements are relatively slow, and there is also a problem of historical data archiving. I need to convert an existing table into a partitioned table for use.
I guess there are several ways, but I don't know how to implement it, and I haven't found the corresponding reference materials
- It is realized by the conversion function, so the original table name will not change. The table of the relational database should be convertible, or the parent-child table solution similar to the pg database. Check the help and find no related functions.
data:
connect createcollection createcompindex
createindex createtimeseries disconnect
dropcompindex dropcontainer dropindex
droptrigger get getcsv
getnoprint getplanjson getplantxt
gettaskplan killsql putrow
queryclose removerow searchcontainer
searchview settimezone showconnection
showcontainer showevent showsql
showtable showtrigger sql
tql tqlanalyze tqlclose
tqlexplain
- By exporting data, creating a partition table and then importing it, but I am not sure how much time this method will take. If I delete the original table directly, there will be risks.
gs[public]> showtable
Database : public
Name Type PartitionId
---------------------------------------------
t2 COLLECTION 13
t3 COLLECTION 27
t1 COLLECTION 55
t1_Partition COLLECTION 55
myHashPartition COLLECTION 101
gs[public]>
- If I pass the DML statement of alter, I am not sure whether the NewSQL interface has this function.I know the following statement is wrong, is there a correct statement?
gs[public]> alter table t1 to t1_Partition;
D20332: An unexpected error occurred while executing a SQL. : msg=[[240001:SQL_COMPILE_SYNTAX_ERROR] Parse SQL failed, reason = Syntax error at or near "to" (line=1, column=15) on updating (sql="alter table t1 to t1_Partition") (db='public') (user='admin') (appName='gs_sh') (clientId='6045b94-4626-4d38-a96a-ff396a16791:7') (clientNd='{clientId=8, address=192.168.5.120:60478}') (address=192.168.5.120:20001, partitionId=6946)]
Hoping someone can tell me how to properly convert an existing table to a partitioned table in GridDB with minimal downtime. thanks
