Skip to main content
Interania

Add a shard key to an existing table

0votes
9updates
261views
Matt Lubrano

Why would I want to do this? If the current table has many cohorts, metrics, sessions, funnels and/or dashboards built out.  We have no easy way to migrate these to a different table.  Also, if you want to keep the current table name, creating a new table  will not allow this.

Delete all table data.  If you're not ok with deleting all the data then this process is probably not right for you.  It may still work for new data going forward but no promises.

ubuntu@ubuntu14:~$ /opt/interana/backend/import_server/delete_table_data.py -c pid6lshidldhusx5akyogwv0y8boqfcn1radaylnuwmx -t mixpanel_events -i

Note that this does not use the -f option to delete all metadata.

Insert one new row into tablemap.table_copies. In my case, I had two string columns as shard keys using the hash function murmur3. The new shard column is of type int. I can't guarantee this process will work if the new shard is also of type string.

First check tablemap.table_copies:

MariaDB [(none)]> select * from tablemap.table_copies; +---------------+----------+------------------+-----------------------------+----------------+---------------------------+-------------------------------+-------+-----------------------+------------------------+-----------------+---------------------+ | table_copy_id | table_id | data_time_period | shard_key | shard_function | shard_function_exceptions | shard_function_exceptions_int | state | shard_function_params | lookup_string_group_id | shard_layout_id | update_time | +---------------+----------+------------------+-----------------------------+----------------+---------------------------+-------------------------------+-------+-----------------------+------------------------+-----------------+---------------------+ | 1 | 1 | 345600000 | properties.distinct_id | murmur3 | NULL | NULL | open | | 0 | 1 | 2015-10-09 15:10:26 | | 2 | 1 | 345600000 | properties.referring_domain | murmur3 | NULL | NULL | open | | 0 | 1 | 2015-10-09 15:10:26 | +---------------+----------+------------------+-----------------------------+----------------+---------------------------+-------------------------------+-------+-----------------------+------------------------+-----------------+---------------------+ 2 rows in set (0.00 sec)

Now add the new shard key

insert into table_copies (table_copy_id,table_id,data_time_period,shard_key,shard_function,shard_function_exceptions,shard_function_exceptions_int,state,shard_function_params,lookup_string_group_id,shard_layout_id,update_time) values (2,1,345600000,'properties.app','less_than_abs',NULL,NULL,'open',2,0,1,'2015-10-09 15:10:26');

View the result showing new shard_key.

MariaDB [(none)]> select * from tablemap.table_copies; +---------------+----------+------------------+-----------------------------+----------------+---------------------------+-------------------------------+-------+-----------------------+------------------------+-----------------+---------------------+ | table_copy_id | table_id | data_time_period | shard_key | shard_function | shard_function_exceptions | shard_function_exceptions_int | state | shard_function_params | lookup_string_group_id | shard_layout_id | update_time | +---------------+----------+------------------+-----------------------------+----------------+---------------------------+-------------------------------+-------+-----------------------+------------------------+-----------------+---------------------+ | 1 | 1 | 345600000 | properties.distinct_id | murmur3 | NULL | NULL | open | | 0 | 1 | 2015-10-09 15:10:26 | | 2 | 1 | 345600000 | properties.referring_domain | murmur3 | NULL | NULL | open | | 0 | 1 | 2015-10-09 15:10:26 | | 3 | 1 | 345600000 | properties.app | less_than_abs | NULL | NULL | open | 2 | 0 | 1 | 2015-10-14 18:08:28 | +---------------+----------+------------------+-----------------------------+----------------+---------------------------+-------------------------------+-------+-----------------------+------------------------+-----------------+---------------------+ 3 rows in set (0.00 sec)

Restart all services. 

ubuntu@config000:~$ /opt/interana/backend/deploy/execute_command_on_tiers.py -c "sudo service interana restart"

Now just reimport your data and boom! You have a new shard key.

  • Was this article helpful?