MariaDB ColumnStore has the ability to query data from just a single PM instead of the whole database through the UM. In order to accomplish this, the infinidb_local_query variable in the my.cnf configuration file is used and maybe set as a default at system wide or set at the session level.
Local PM query can be enabled system wide during the install process when running the install script postConfigure. Answer 'y' to this prompt during the install process.
NOTE: Local Query Feature allows the ability to query data from a single Performance Module. Check MariaDB ColumnStore Admin Guide for additional information. Enable Local Query feature? [y,n] (n) >
To enable the use of the local PM Query at the instance level, specify
infinidb_local_query =1 (enabled) in the my.cnf configuration file at /usr/local/mariadb/columnstore/mysql. The default is 0 (disabled).
To enable/disable the use of the local PM Query at the session level, the following command is used. Once the session has ended, any subsequent session will return to the default for the instance.
set infinidb_local_query = n where n is: * 0 (disabled) * 1 (enabled)
With the infinidb_local_query variable set to 1 (default with local PM Query):
mcsmysql -e 'select * from source_schema.source_table;' –N | /usr/local/Calpont/bin/cpimport target_schema target_table -s '\t' –n1
With the infinidb_local_query variable set to 0 (default with local PM Query):
Create a script (i.e., extract_query_script.sql in our example) similar to the following:
set infinidb_local_query=0; select fact.column1, dim.column2 from fact join dim using (key) where idbPm(fact.key) = idbLocalPm();
The infinidb_local_query is set to 0 to allow query across all PMs.
The query is structured so that the UM process on the PM node gets the fact table data locally from the PM node (as indicated by the use of the idbLocalPm() function), while the dimension table data is extracted from all the PM nodes.
Then you can execute the script to pipe it directly into cpimport:
mcsmysql source_schema -N < extract_query_script.sql | /usr/local/mariadb/columnstore/bin/cpimport target_schema target_table -s '\t' –n1
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.