How to insert data in Apache calcite

Question

I'm inserting data form SQL to File through Apache calcite

 Class.forName("org.apache.calcite.jdbc.Driver");
 Connection connection = DriverManager.getConnection("jdbc:calcite:");
 CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
 SchemaPlus rootSchema = calciteConnection.getRootSchema(); 
 JdbcSchema jdbcSchema = JdbcSchema.create(rootSchema, "TRUNKDB", dataSource,  null, "dbo");       
 rootSchema.add("XXXX", jdbcSchema);

 /*CSV Schema*/
 File csvDir = new File("/home/nanobi/Drill/CSV/");
 // SchemaPlus schema = rootSchema.add("s", new CsvSchema(csvDir,null)); 
 rootSchema.add("CSV", new CsvSchema(csvDir, Flavor.SCANNABLE));

 Statement statement = connection.createStatement();

 int resultSet =   statement.executeUpdate("INSERT into \"CSV\".\"p\"(\"Name\") select \"name\" from \"TRUNKDB\".\"nbmdc_nanomarts\"");

i'm getting bellow error

Exception in thread "main" java.sql.SQLException: Error while executing SQL "INSERT into "CSV"."p"("Name") select 'p' from "TRUNKDB"."nbmdc_nanomarts"": Node [rel#29:Subset#3.ENUMERABLE.[]] could not be implemented; planner state:

Root: rel#29:Subset#3.ENUMERABLE.[]
Original rel:


    Sets:
    Set#0, type: RecordType(VARCHAR(45) row_id, VARCHAR(45) si_id, VARCHAR(500) name, VARCHAR(500) description, VARCHAR(255) icon_path,
VARCHAR(255) icon_content, VARCHAR(255) active_flag, TIMESTAMP(3)
created_datetime, VARCHAR(45) created_by_user_id, TIMESTAMP(3)
updated_datetime, VARCHAR(45) updated_by_user_id, VARCHAR(500)
nanomart_xml_filepath, VARCHAR(255) db_username, VARCHAR(255)
db_user_password, VARCHAR(255) dbase_name, VARCHAR(255) db_url,
VARCHAR(255) db_schema_name, CHAR(1) is_mandatory, CHAR(1)
is_load_lock, VARCHAR(45) mart_type, VARCHAR(255) db_driver,
VARCHAR(255) load_frequency, CHAR(1) is_date_table, CHAR(1) is_alias,
VARCHAR(500) nbmdc_n, VARCHAR(45) master_flag, VARCHAR(50)
nbmdm_repository_row_id, CHAR(1) is_hierarchical, VARCHAR(4000)
inplacedetail)
        rel#8:Subset#0.JDBC.TRUNKDB.[], best=rel#0, importance=0.6561
            rel#0:JdbcTableScan.JDBC.TRUNKDB.[](table=[TRUNKDB, nbmdc_nanomarts]), rowcount=100.0, cumulative cost={100.0 rows, 101.0
cpu, 0.0 io}

Michael Mior · Answer 1 · 2016-10-19T15:21:11.727

1

Unfortunately Calcite doesn't currently support modification of CSV files. Modification has only been worked on for JDBC tables.

edited Oct 19 '16 at 15:21

answered Oct 19 '16 at 15:06

Michael Mior

28,107
9
89
113

statement.executeUpdate("insert into \"TRUNKDB\".\"table3\"(\"name\") select b.BRANCH_CODE from \"CSV\".\"branchmerge\" b"); Now i am inserting to MSSQL it shows the same error – Prasad V S Oct 20 '16 at 05:28
Sorry that it wasn't clear. Modification support does not work out of the box. There has been some work in this area, but support is not complete. – Michael Mior Oct 20 '16 at 11:21
i want to whether now we can perform insert and update into CSV or not? – kavyansh Apr 02 '20 at 06:41

How to insert data in Apache calcite

1 Answers1