526互联

数据库_DuckDB_数据库应用示例

发布时间 2024-01-10 12:15:37作者: 辰令

通用的问题

数据库存储的位置在那里;
数据库的所有者是谁；
其它用户如何来访问这个数据库；

duckdb

  DuckDB 是阿姆斯特丹 Centrum Wiskunde&Informatica(CWI)数学和理论计算研究中心的学者们的创意，
它嵌入在一个主机进程中。无需安装、更新或维护 DBMS 服务器软件

程序
   DuckDB 的二进制库+头文件
数据文件
   .db  以及 .db.wal
结构--Database, Catalog and Schema  Tables and Views  Rows Columns
  file--用户的数据库名称--databasename-database_oid
     information_schema  pg_catalog main
  system 
     information_schema main pg_catalog 
  temp
     information_schema main pg_catalog
DuckDB 的模块介绍--代码实现
   SQL Parser -->  Logical Planner -->  Optimizer --> Physical Planner -->  Execution Engine
   Transaction and Concurrency Control
   Storage 
###概念
  1.information_schema.schemat
    schema_name	Name of the schema.	VARCHAR	'main'
  2.pg_catalog 
       standard PostgreSQL pg_catalog functions and views for compatibility with PostgreSQL database and SQL syntax
  3.main
     表 视图 索引 存储过程 数据类型  函数  扩展  保留关键字
duckdb一些主要的概念词
  databases, schemas, 
     indexes, sequences, extensions, functions, types, configuration settings, and reserved keywords
  information_schema.tables
     The schema the table or view belongs to.	VARCHAR	'main'
驱动：
  duckdb_jdbc-0.9.2.jar
工作方式
   DuckDB 支持内存型和持久化型两种工作模式
      内存型不持久化数据，采用 InMemoryBlockManager 来管理数据 	
	  持久化型数据        采用 SingleFileBlockManager 来管理外存上的数据

从命令行中学习-SQL环境下Catalog和Schema都属于抽象概念

 数据库--databasename-database_oid
    current database
 数据库 schema含义数据库Schema有两种含义，
    一种是概念上的Schema，指的是一组DDL语句集，该语句集完整地描述了数据库的结构。
	还有一种是物理上的 Schema，指的是数据库中的一个名字空间  
    current_schemas(true)	['temp', 'main', 'pg_catalog']		
    Catalog Schema User
	    user  username  password   	   
1.Cluster > Catalog > Schema > Table > Columns & Rows
    So in both Postgres and the SQL Standard we have this containment hierarchy:
    
    A computer may have one cluster or multiple.
    A database server is a cluster.
    A cluster has catalogs. ( Catalog = Database )
    Catalogs have schemas. (Schema = namespace of tables, and security boundary)
    Schemas have tables.
    Tables have rows.
    Rows have values, defined by columns.
	
2.PostgreSQL中内建了对表常规访问的支持,
     但是所有的索引方法则是在pg_am中描述
	 
3.Constraints，是限制、约束
    constraint_type 约束类型   CHECK, FOREIGN KEY, PRIMARY KEY, NOT NULL, UNIQUE.
	       CHECK约束 是指约束表中某一个或者某些列中可接受的数据值或者数据格式
		   唯一约束
    5种完整性约束：
              1)NOT NULL:    指定某列不能为空
              2)UNIQUE:      指定某列或某几列不能重复
              3)PRIMARY KEY: 指定该列的值可以唯一地标识该条记录
              4)FOREIGN KEY: 指定该行记录从属于主表中的一条记录，主要用于保证参照完整性
              5)CHECK:       指定一个布尔表达式，用于指定对应列的值必须满足该表达式
                             【mysql不支持check】
    2类约束：采用列级约束语法或者表级约束语法
              1)单列约束：
              2)多列约束：
    指定约束的时机：
              1)建表的同时为相应的数据列指定约束
              2)建表后创建，以修改表的方式来增加约束（用alter来修改）		 
	select * from duckdb_settings();
	select *from duckdb_keywords();
	
	select * from duckdb_databases();
	select * from duckdb_schemas();
	select * from duckdb_functions()
	select * from duckdb_tables()
    select * from duckdb_views();
	select * from duckdb_indexes();
    select * from duckdb_columns();
	select * from duckdb_types()
    select * from duckdb_constraints();
    select * from duckdb_sequences();		
    select * from duckdb_extensions();	
	select * from duckdb_optimizers();

其他类似的数据库

MySQL初始化完成后会创建出4个系统库,
    分别是information_schema、performance_schema、mysql、sys
	infomation_schema 提供数据库的元数据，比如数据库名、表名、索引等，可以当作字典表
	mysql              是 mysql的核心库，主要存储数据库用户、权限等 mysql 自身需要使用的信息
	performance_schema 用于收集数据库服务器的性能数据，以便分析问题。比如 SQL 的执行次数、耗时、锁等信息
postgres 刚初始化的集群中，默认有一个数据库 postgres，有一个超级管理员用户，默为当前系统的用户
     初始化数据库后默认的表空间有pg_default、pg_global	
	
SQLite的默认库是一个空数据库，SQLite 在创建数据库时创建架构表
  每个 SQLite 数据库都包含一个 "schema table" ，用于存储该数据库的架构。
       数据库的架构是对数据库中包含的所有其他表、索引、触发器和视图的描述
	 main.sqlite_schema " 或 "temp.sqlite_schema"  
   SQLite 数据库都有一个叫 SQLITE_MASTER 的表， 
	里面存储着数据库的数据结构（表结构、视图结构、索引结构等），只可以对它使用查询语句  
   sqlite_schema 替代名称也被认可，包括： sqlite_master  sqlite_temp_schema  sqlite_temp_master

数据导入

import duckdb
import os

if __name__=="__main__":
    csv_dir = r"~/duckdb_exp"
    file_nm = r"roadline.csv"
    ##  dbeaver中 duckdb 可以不导入到数据库中，就可以直接查询<前提是已经安装好debeaver 以及duckdb 以及dbeaver的duckdb驱动> 
    ## dbeaver “新建 SQL 编辑器”，这样我们就可以在里面写 SQL 语句
    ## 直接可以查询出数据结果，而不需要像一般数据库需要先建表、导入数据后才能查询
    ##  select num,sum(pcd_cnt) from "~/duckdb_exp/road.csv" group by num    
    input_csv_file = os.path.join(csv_dir,file_nm)
    # 如果一张表我们要经常查询还是需要将其导入到数据库
    out_file_db = os.path.join(csv_dir,'file.db')
    if not os.path.exists(out_file_db):
        table_nm = "road_info"
        conn = duckdb.connect(out_file_db)
        c = conn.cursor()
        sql_cmd = ("CREATE TABLE {0} AS SELECT * FROM read_csv_auto('{1}') ").format(table_nm,input_csv_file)
        ## sample_size=-1，这样可以参数数据中的所有行，来创建适当的数据类型。
        c.execute(sql_cmd)
        conn.close()

参考

duckdb从入门到精通：数据导入 https://zhuanlan.zhihu.com/p/627565479

和pandas一起使用

import pandas as pd
import duckdb
import os

if __name__ =="__main__":
    root_dir = r"/data/orig_stat_src"
    class_grade_task = os.path.join(root_dir,r"class_grade_task.txt")

    ##data01 
    task_id = pd.read_csv(class_grade_task,sep="\t")
    task_id['ori_grade_nm'] = task_id["grade_nm"].str[:-4]
    print(task_id.head())
    ##data02 
    grade_path = os.path.join(root_dir,"grade_path.txt")
    grade_path_df = pd.read_csv(grade_path,sep="\t")
    grade_path_df['ori_grade_nm'] = grade_path_df["grade_path"].str.split("/").str[-1].str.replace("grade_","").str.replace("cor","corr").str[:-4]
    print(grade_path_df.head())
    #grade_path_df['ori_grade_nm'].to_csv("/data/test.txt",index=None)
	#####data03
    info_1_df  =pd.merge(task_id, grade_path_df, how = 'left',  on = "ori_grade_nm")
	### duckdb 
    data = duckdb.query('select task_id,max(task_nm) as task_nm, sum(json_cnt), sum(obj_cnt) from info_1_df group by task_id').df()
	print(data)

参考

 写规则引擎	  
 RandomFractals duckdb-sql-tools https://github.com/RandomFractals/duckdb-sql-tools/discussions 
 https://github.com/RandomFractals
 https://livebook.manning.com/book/duckdb-in-action/welcome/v-4/	
 https://github.com/duckdb-in-action/examples/tree/main/ch03  
  数据库 schema与catalog https://www.cnblogs.com/ECNB/p/4611309.html
  https://stackoverflow.com/questions/7022755/whats-the-difference-between-a-catalog-and-a-schema-in-a-relational-database