java 操作clickhouse数据库

//  设置clickhouse 的参数，如：账户、密码
val properties = new ClickHouseProperties
properties.setSocketTimeout(ckTimeOut)
properties.setUser(ckUser)
properties.setPassword(ckPass)
 

1、clickhouse判断某表是否存在

/*
database： 数据库名
tablName： 表名
*/
def tableExists(database: String, tablName: String): Boolean = {
	//  获取 clickhouse连接 con
	val dataSource = new ClickHouseDataSource("clickhouse地址", properties)
    conn = dataSource.getConnection.asInstanceOf[ClickHouseConnectionImpl]
    val set = conn.getMetaData.getTables(null, database, tablName, null)
    if (set.next()) {
      return true
    } else {
      println("不存在")
      return false
    }
  }
 

2、判断是否存在该列

/**
tablesName：数据库名.表名
cols： 列名
*/

  def colsExists(tablesName: String, cols: String): Boolean = {
  	//  获取 clickhouse连接 con
  	val dataSource = new ClickHouseDataSource("clickhouse地址", properties)
    conn = dataSource.getConnection.asInstanceOf[ClickHouseConnectionImpl]
    //  创建statement 
    val statement = conn.createStatement()
    val resultSet = statement.executeQuery("select * from " + tablesName)
    println("select * from " + tablesName)
    val data = resultSet.getMetaData()
    val count = data.getColumnCount
    for (i <- 1 to count) {
      val str = data.getColumnName(i)
      println(str + cols)
      val bool = str.contains(cols)
      println(bool)
      return bool
    }
    return false
  }
 

3、创建表

  //创建所有节点的原始表表
  //  sql：sql语句
  // 注意：用户名和密码可以不写，不添加就可以
  def createTable(sql: String): Unit = {
    val arr: Array[String] = ckClusterUrl.split(",")
    import java.sql.{Connection, DriverManager}
    Class.forName("com.github.housepower.jdbc.ClickHouseDriver")
    println("表")
      val connection: Connection = DriverManager.getConnection("clickhouse地址", "用户名", "密码")
      val statement = connection.createStatement()
      statement.executeQuery(sql)
  }
 

4、获取数据库中所有表名

  // 获取数据库中所有表名
  // db： 数据库名
  // 数据库地址
  def getOherTables(db: String , ckMasterUrl:String):ArrayBuffer[String] ={
    val tableList = new ArrayBuffer[String]()
    val dataSource = new ClickHouseDataSource(ckMasterUrl, properties)
    val  conn = dataSource.getConnection.asInstanceOf[ClickHouseConnectionImpl]
    val set: ResultSet = conn.getMetaData.getTables(null, db, null, null)
    while (set.next()){
      val table = set.getString("TABLE_NAME")
      tableList += table
    }
    tableList
  }
 

5、根据某字段内容删除数据

//根据tables和tag删除表中数据
//db：数据库名
//table：表名
//resourceId： 一个字段值
  def deleteDateBytableAndTag(db: String, table: String, resourceId: String) = {
    try {
      val arr: Array[String] = ckClusterUrl.split(",")
      for (e <- arr) {
        val source = new ClickHouseDataSource(e, properties)
        val clickHouseConnectionImpl: ClickHouseConnectionImpl = source.getConnection.asInstanceOf[ClickHouseConnectionImpl]
        var sql = "ALTER TABLE " + db + "." + table + " DELETE WHERE" + " resourceId" + "='" + resourceId + "'"
        clickHouseConnectionImpl.createStatement().executeQuery(sql)
      }
    } catch {
      case e: Exception =>
        e.printStackTrace()
    }
  }
 

6、创建数据库库名

// 创建数据库库名
// dbName： 数据库名
def createDatabase(dbName: String): Unit ={
    val urls: Array[String] = ckClusterUrl.split(",")
    val sql = "create database " + dbName
    for (elem <- urls) {
    try{
      val source = new ClickHouseDataSource(elem , properties)
      val impl = source.getConnection().asInstanceOf[ClickHouseConnectionImpl]
      impl.createStatement().executeQuery(sql)
      println("创建 ==="+elem+ "======成功")
    }catch{
      case e:Exception =>
        e.printStackTrace()
        println("创建 ==="+elem+ "======失败")
      }
    }
  }
 

7、获取clickhouse数据库中的表的结构

  def readTaleType(database: String, table: String): String = {
    var str: String = null
    val sql = String.format("SHOW CREATE TABLE %s", database + "." + table)
    val statement = conn.prepareStatement(sql)
    val resultSet = statement.executeQuery()
    while (resultSet.next()) {
      str = resultSet.getString(1)
    }
    return str
  }