cassandra system.schema_columns insert in other table with modifications

Question

i have the following select:

select * from system.schema_columns where keyspace_name = 'automotive' and columnfamily_name = 'cars';

and i want to insert data returned by this into another table with some modifications:

-i want to insert the data type of column

-remove audit columns like created_at, created_by etc..

in my sql we can do this by:

insert into formUtil(table_name, column_name, ordinal_position, is_nullable, data_type)
SELECT 
    col.table_name, 
    col.column_name, 
    col.ordinal_position, 
    case when col.is_nullable = 'YES' then 1 else 0 end, 
    col.data_type
from 
    information_schema.COLUMNS col 
where 
    col.table_schema = 'i2cwac' and
    col.column_name not in ('id','modifiedAt','modifiedBy','createdAt','createdBy') and
    col.table_name = 'users';

how we can do this in cassandra ?

doanduyhai doanduyhai · Accepted Answer · 2016-04-14T14:14:13

You can achieve this by using Spark

import java.nio.ByteBuffer;
import com.datastax.spark.connector._;

case class SchemaColumns(keyspaceName: String, tableName: String, clusteringOrder: String, columnNameBytes: ByteBuffer, kind: String, position: Int, type: String)
case class AnotherTable(keyspaceName: String, tableName: String, type: String)

sc.cassandraTable[SchemaColumns]("system", "schema_columns")
    .map(schemaColumns -> AnotherTable(schemaColumns.keyspaceName,schemaColumns.tableName, schemaColumns.type)
    .saveToCassandra("my_keyspace","another_table")

cassandra system.schema_columns insert in other table with modifications

1 Answers