序言

咱们在 Apache Calcite 动态数据治理框架介绍 介绍了 calcite 的基本功能,本文一起来看一下如何实现一个 csv 的 sql 查问。

入门例子

依赖

<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0"         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">    <modelVersion>4.0.0</modelVersion>    <groupId>org.example</groupId>    <artifactId>calcite-learn</artifactId>    <version>1.0-SNAPSHOT</version>    <packaging>pom</packaging>    <modules>        <module>calcite-learn-basic</module>    </modules>    <properties>        <maven.compiler.source>8</maven.compiler.source>        <maven.compiler.target>8</maven.compiler.target>        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>        <calcite.version>1.20.0</calcite.version>    </properties>    <dependencies>        <dependency>            <groupId>org.apache.calcite</groupId>            <artifactId>calcite-core</artifactId>            <version>${calcite.version}</version>        </dependency>        <dependency>            <groupId>org.apache.calcite</groupId>            <artifactId>calcite-example-csv</artifactId>            <version>${calcite.version}</version>        </dependency>        <!-- Add other dependencies, e.g., database driver -->    </dependencies>    <build>        <plugins>            <plugin>                <groupId>org.apache.maven.plugins</groupId>                <artifactId>maven-compiler-plugin</artifactId>                <version>3.8.1</version>                <configuration>                    <source>1.8</source>                    <target>1.8</target>                </configuration>            </plugin>        </plugins>    </build></project>

测试 csv

创立文件夹:

D:\github\calcite-learn\calcite-learn-basic\src\main\resources\csv

上面防对应的测试 csv 文件:

  • depts.csv
EMPNO:long,NAME:string,DEPTNO:int,GENDER:string,CITY:string,EMPID:int,AGE:int,SLACKER:boolean,MANAGER:boolean,JOINEDAT:date100,"Fred",10,,,30,25,true,false,"1996-08-03"110,"Eric",20,"M","San Francisco",3,80,,false,"2001-01-01"110,"John",40,"M","Vancouver",2,,false,true,"2002-05-03"120,"Wilma",20,"F",,1,5,,true,"2005-09-07"130,"Alice",40,"F","Vancouver",2,,false,true,"2007-01-01"

测试类

package com.github.houbb.calcite.learn.basic;import org.apache.calcite.adapter.csv.CsvSchema;import org.apache.calcite.adapter.csv.CsvTable;import org.apache.calcite.jdbc.CalciteConnection;import org.apache.calcite.schema.SchemaPlus;import java.io.File;import java.sql.*;import java.util.Properties;public class CsvDemo {    public static void main(String[] args) throws Exception {        // 0.获取csv文件的门路,留神获取到文件所在下层门路就能够了        String path = "D:\\github\\calcite-learn\\calcite-learn-basic\\src\\main\\resources\\csv\\";        // 1.构建CsvSchema对象,在Calcite中,不同数据源对应不同Schema,比方CsvSchema、DruidSchema、ElasticsearchSchema等        CsvSchema csvSchema = new CsvSchema(new File(path), CsvTable.Flavor.SCANNABLE);        // 2.构建Connection        // 2.1 设置连贯参数        Properties info = new Properties();        // 不辨别sql大小写        info.setProperty("caseSensitive", "false");        // 2.2 获取规范的JDBC Connection        Connection connection = DriverManager.getConnection("jdbc:calcite:", info);        // 2.3 获取Calcite封装的Connection        CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);        // 3.构建RootSchema,在Calcite中,RootSchema是所有数据源schema的parent,多个不同数据源schema能够挂在同一个RootSchema下        // 以实现查问不同数据源的目标        SchemaPlus rootSchema = calciteConnection.getRootSchema();        // 4.将不同数据源schema挂载到RootSchema,这里增加CsvSchema        rootSchema.add("csv", csvSchema);        // 5.执行SQL查问,通过SQL形式拜访csv文件        String sql = "select * from csv.depts";        Statement statement = calciteConnection.createStatement();        ResultSet resultSet = statement.executeQuery(sql);        // 6.遍历打印查问后果集        printResultSet(resultSet);    }    public static void printResultSet(ResultSet resultSet) throws SQLException {        // 获取 ResultSet 元数据        ResultSetMetaData metaData = resultSet.getMetaData();        // 获取列数        int columnCount = metaData.getColumnCount();        System.out.println("Number of columns: " + columnCount);        // 遍历 ResultSet 并打印后果        while (resultSet.next()) {            // 遍历每一列并打印            for (int i = 1; i <= columnCount; i++) {                String columnName = metaData.getColumnName(i);                String columnValue = resultSet.getString(i);                System.out.println(columnName + ": " + columnValue);            }            System.out.println(); // 换行        }    }}

测试成果

Number of columns: 10EMPNO: 100NAME: FredDEPTNO: 10GENDER: CITY: EMPID: 30AGE: 25SLACKER: trueMANAGER: falseJOINEDAT: 1996-08-03EMPNO: 110NAME: EricDEPTNO: 20GENDER: MCITY: San FranciscoEMPID: 3AGE: 80SLACKER: nullMANAGER: falseJOINEDAT: 2001-01-01EMPNO: 110NAME: JohnDEPTNO: 40GENDER: MCITY: VancouverEMPID: 2AGE: nullSLACKER: falseMANAGER: trueJOINEDAT: 2002-05-03EMPNO: 120NAME: WilmaDEPTNO: 20GENDER: FCITY: EMPID: 1AGE: 5SLACKER: nullMANAGER: trueJOINEDAT: 2005-09-07EMPNO: 130NAME: AliceDEPTNO: 40GENDER: FCITY: VancouverEMPID: 2AGE: nullSLACKER: falseMANAGER: trueJOINEDAT: 2007-01-01

参考资料

Apache Calcite 疾速入门指南

Apache Calcite精简入门与学习领导

本文由博客一文多发平台 OpenWrite 公布!