我想在Cassandra中选择表的特定字段,并将它们插入到另一个表中。在SQL Server中,我会像这样操作:
INSERT INTO Users(name,family)
SELECT name,family FROM Users
如何在cassandra-cli或cqlsh中执行此操作?我想在Cassandra中选择表的特定字段,并将它们插入到另一个表中。在SQL Server中,我会像这样操作:
INSERT INTO Users(name,family)
SELECT name,family FROM Users
如何在cassandra-cli或cqlsh中执行此操作?COPY keyspace.columnfamily1 (column1, column2,...) TO 'temp.csv';
COPY keyspace.columnfamily2 (column1, column2,...) FROM 'temp.csv';
在这里,您需要提供您的键空间(模式名称),并将columnfamilyname1替换为要复制的表,在columnfamily2中给出您想要复制到的表名称。
是的,这是CQL的解决方案,但我从未尝试过使用CLI。
对于不是很大的表格,可以将文件保存并使用匿名管道:
cqlsh -e "COPY keyspace.src_table (col1, col2, ...,ColN ) TO STDOUT WITH HEADER=false" | cqlsh -e "COPY keyspace.target_table (col1, col2, ...,ColN ) FROM STDIN"
对于非常大的数据集,这种方法行不通。应该探索每个标记范围的策略。
对于非常大的表,CQLSH在处理COPY TO/FROM时可能会遇到困难。以下是如何通过本地令牌范围扫描表并将所需列从一个表复制到另一个表的方法。由于它使用本地副本,因此必须在数据中心的每个节点上执行:
#!/bin/bash
#
# Script to COPY from a SOURCE table's select columns over a TARGET table
# Assumes the following:
#
# * The SOURCE table is very large - otherwise just try:
# cqlsh -e "COPY keyspace.src_table (col1, col2, ...,ColN ) TO STDOUT WITH HEADER=false" \
# |cqlsh -e "COPY keyspace.tgt_table (col1, col2, ...,ColN ) FROM STDIN"
# * SOURCE AND TARGET TABLES are in the SAME KEYSPACE
# * TARGET columns are named the SAME as SOURCE
#
# The script sweeps thru the local tokens to copy only the local data over to the new table
# Therefore, this script needs to run on every node on the datacenter
#
# Set these variables before executing
#
USR=cassandra
PWD=password
KSP=my_keyspace
SRC=src_table
COL="col1, col2, col3"
PKY="col1"
TGT=tgt_table
CQLSH="cqlsh -u ${USR} -p ${PWD} -k ${KSP}"
function getTokens(){
for i in $($CQLSH -e "select tokens from system.local;" | awk -F, '/{/{print $0}' | tr -d '{' | tr -d '}' | tr -d ','); do
echo ${i//\'/}
done | sort -n
}
function getDataByTokenRange(){
i=0
tokens=($(getTokens))
while [ ${i} -lt ${#tokens[@]} ]; do
[ ${i} -eq 0 ] && echo "SELECT ${COL} FROM ${SRC} WHERE token(${PKY}) <= ${tokens[i]};"
[ "${tokens[i+1]}" ] && echo "SELECT ${COL} FROM ${SRC} WHERE token(${PKY}) > ${tokens[i]} AND token(${PKY}) <= ${tokens[i+1]};"
[ ! "${tokens[i+1]}" ] && echo "SELECT ${COL} FROM ${SRC} WHERE token(${PKY}) > ${tokens[i]};"
((i++))
done
}
function cqlExec(){
while IFS='' read -r cql || [[ -n "$line" ]]; do
$CQLSH -e "CONSISTENCY LOCAL_ONE; $cql" \
|awk -F\| '( !/LOCAL_ONE/ && !/'"${COL/, /|}"'/ && !/^\-+/ && !/^\([0-9]+ rows)/ && !/^$/ ){print $0}' \
|sed -e 's/^[ ]*//g' -e 's/[ ]*|[ ]*/|/g' \
|$CQLSH -e "COPY ${TGT} (${COL}) FROM STDIN WITH DELIMITER = '|' and HEADER=false;"
[ "$?" -gt 0 ] && echo "ERROR: Failed to import data from command: ${command}"
done < "$1"
}
main(){
echo "Begin processing ..."
getDataByTokenRange > getDataByTokenRange.ddl
cqlExec getDataByTokenRange.ddl
echo "End procesing"
}
main