首页 > 其他分享 >DuckDB - Study Notes 5

DuckDB - Study Notes 5

时间:2025-01-14 18:15:06浏览次数:1  
标签:sqlite false name my Study DuckDB varchar true Notes

 

D SELECT * FROM duckdb_extensions();
┌──────────────────┬─────────┬───────────┬──────────────┬───┬───────────────────┬───────────────────┬───────────────────┬────────────────┐
│  extension_name  │ loaded  │ installed │ install_path │ … │      aliases      │ extension_version │   install_mode    │ installed_from │
│     varchar      │ boolean │  boolean  │   varchar    │   │     varchar[]     │      varchar      │      varchar      │    varchar     │
├──────────────────┼─────────┼───────────┼──────────────┼───┼───────────────────┼───────────────────┼───────────────────┼────────────────┤
│ arrow            │ false   │ false     │              │ … │ []                │                   │                   │                │
│ autocomplete     │ true    │ true      │ (BUILT-IN)   │ … │ []                │                   │ STATICALLY_LINKED │                │
│ aws              │ false   │ false     │              │ … │ []                │                   │                   │                │
│ azure            │ false   │ false     │              │ … │ []                │                   │                   │                │
│ delta            │ false   │ false     │              │ … │ []                │                   │                   │                │
│ excel            │ false   │ false     │              │ … │ []                │                   │                   │                │
│ fts              │ true    │ true      │ (BUILT-IN)   │ … │ []                │ v1.1.3            │ STATICALLY_LINKED │                │
│ httpfs           │ false   │ false     │              │ … │ [http, https, s3] │                   │                   │                │
│ iceberg          │ false   │ false     │              │ … │ []                │                   │                   │                │
│ icu              │ true    │ true      │ (BUILT-IN)   │ … │ []                │ v1.1.3            │ STATICALLY_LINKED │                │
│ inet             │ false   │ false     │              │ … │ []                │                   │                   │                │
│ jemalloc         │ true    │ true      │ (BUILT-IN)   │ … │ []                │ v1.1.3            │ STATICALLY_LINKED │                │
│ json             │ true    │ true      │ (BUILT-IN)   │ … │ []                │ v1.1.3            │ STATICALLY_LINKED │                │
│ motherduck       │ false   │ false     │              │ … │ [md]              │                   │                   │                │
│ mysql_scanner    │ false   │ false     │              │ … │ [mysql]           │                   │                   │                │
│ parquet          │ true    │ true      │ (BUILT-IN)   │ … │ []                │ v1.1.3            │ STATICALLY_LINKED │                │
│ postgres_scanner │ false   │ false     │              │ … │ [postgres]        │                   │                   │                │
│ shell            │ true    │ true      │              │ … │ []                │                   │ STATICALLY_LINKED │                │
│ spatial          │ false   │ false     │              │ … │ []                │                   │                   │                │
│ sqlite_scanner   │ false   │ false     │              │ … │ [sqlite, sqlite3] │                   │                   │                │
│ substrait        │ false   │ false     │              │ … │ []                │                   │                   │                │
│ tpcds            │ false   │ false     │              │ … │ []                │                   │                   │                │
│ tpch             │ true    │ true      │ (BUILT-IN)   │ … │ []                │ v1.1.3            │ STATICALLY_LINKED │                │
│ vss              │ false   │ false     │              │ … │ []                │                   │                   │                │
├──────────────────┴─────────┴───────────┴──────────────┴───┴───────────────────┴───────────────────┴───────────────────┴────────────────┤
│ 24 rows                                                                                                            9 columns (8 shown) │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

 

 

D INSTALL sqlite;
100% ▕████████████████████████████████████████████████████████████▏
D LOAD sqlite;

Executing the INSTALL command causes DuckDB to download the sqlite extension and then install it into your local DuckDB installation. The LOAD command is needed to enable the extension for use in your current session.

D SELECT extension_name, installed, loaded
  FROM duckdb_extensions()
  WHERE extension_name = 'sqlite_scanner';
┌────────────────┬───────────┬─────────┐
│ extension_name │ installed │ loaded  │
│    varchar     │  boolean  │ boolean │
├────────────────┼───────────┼─────────┤
│ sqlite_scanner │ true      │ true    │
└────────────────┴───────────┴─────────┘

With the extension installed, DuckDB has now acquired the new ability to read an external SQLite database. We can open the my_sqlite.db SQLite database file using the ATTACH command:

D ATTACH 'my_sqlite.db' (TYPE sqlite);

D SELECT *
  FROM my_sqlite.countries_sqlite;
Invalid Error: Failed to prepare query "SELECT type FROM sqlite_master WHERE lower(name)=lower('countries_sqlite');": file is not a database

 

D USE my_sqlite;
D SHOW TABLES;
Invalid Error: Failed to prepare query "SELECT name FROM sqlite_master WHERE type='table'": file is not a database

用右键另存为下载的my_sqlite.db文件有问题,重新下载raw文件后这个error不再出现,但似乎这个数据库文件中没有表:

D LOAD sqlite;
D ATTACH 'my_sqlite.db' (TYPE sqlite);
D SHOW TABLES;
┌─────────┐
│  name   │
│ varchar │
├─────────┤
│ 0 rows  │
└─────────┘
D SHOW ALL TABLES;
INTERNAL Error: Information loss on integer cast: value -5760601708572726771 outside of target range [-9223372036854775808, 9223372036854775807]
This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.
For more information, see https://duckdb.org/docs/dev/internal_errors

 

D LOAD sqlite;
D ATTACH 'my_sqlite.db' (TYPE sqlite);
D USE my_sqlite;
D SHOW TABLES;
┌──────────────────┐
│       name       │
│     varchar      │
├──────────────────┤
│ countries_sqlite │
└──────────────────┘

 

In DuckDB, the difference between the SHOW TABLES and SHOW ALL TABLES commands is subtle but important. Here's how they differ:

1. SHOW TABLES:

  • This command lists all the user-defined tables in the currently active schema.
  • It will only show tables that have been explicitly created by the user (not system or internal tables).

2. SHOW ALL TABLES:

  • This command provides a broader overview and lists all tables, including system tables and user-defined tables within the current schema.
  • It will also show any internal or hidden tables used by DuckDB for its internal operations.

 

D SELECT *
  FROM my_sqlite.countries_sqlite;
┌─────────┬──────────────────────────────────┐
│ country │               name               │
│ varchar │             varchar              │
├─────────┼──────────────────────────────────┤
│ AD      │ Andorra                          │
│ AE      │ United Arab Emirates             │
│ AF      │ Afghanistan                      │
│ AG      │ Antigua and Barbuda              │
│ AI      │ Anguilla                         │
│ AL      │ Albania                          │
│ AM      │ Armenia                          │
│ AN      │ Netherlands Antilles             │
│ AO      │ Angola                           │
│ AQ      │ Antarctica                       │
│ AR      │ Argentina                        │
│ AS      │ American Samoa                   │
│ AT      │ Austria                          │
│ AU      │ Australia                        │
│ AW      │ Aruba                            │
│ AZ      │ Azerbaijan                       │
│ BA      │ Bosnia and Herzegovina           │
│ BB      │ Barbados                         │
│ BD      │ Bangladesh                       │
│ BE      │ Belgium                          │
│ ·       │   ·                              │
│ ·       │   ·                              │
│ ·       │   ·                              │
│ UG      │ Uganda                           │
│ UM      │ U.S. Minor Outlying Islands      │
│ US      │ United States                    │
│ UY      │ Uruguay                          │
│ UZ      │ Uzbekistan                       │
│ VA      │ Vatican City                     │
│ VC      │ Saint Vincent and the Grenadines │
│ VE      │ Venezuela                        │
│ VG      │ British Virgin Islands           │
│ VI      │ U.S. Virgin Islands              │
│ VN      │ Vietnam                          │
│ VU      │ Vanuatu                          │
│ WF      │ Wallis and Futuna                │
│ WS      │ Samoa                            │
│ XK      │ Kosovo                           │
│ YE      │ Yemen                            │
│ YT      │ Mayotte                          │
│ ZA      │ South Africa                     │
│ ZM      │ Zambia                           │
│ ZW      │ Zimbabwe                         │
├─────────┴──────────────────────────────────┤
│ 245 rows (40 shown)              2 columns │
└────────────────────────────────────────────┘

 

标签:sqlite,false,name,my,Study,DuckDB,varchar,true,Notes
From: https://www.cnblogs.com/zhangzhihui/p/18671340

相关文章

  • DuckDB:星号(*)表达式完整指南
    本文介绍DuckDB星号的用法,尤其是与其他数据库不同的用法,掌握它可以快速帮你选择和转换列。常规用法首先我们介绍星号表达式的常规用法,这与其他数据库功能一样。#选择from子句中所有列SELECT*FROMtable_name;#计算表中的行数,DuckDB支持省略*号SELECTcount(......
  • 安装 php 开发环境(基于 php study)
    目录安装 php 开发环境(基于 phpstudy)创建简单博客示例代码下载 phpstudy:可以从官网(xp.cn)下载,也可以在内网(如提到的 6.100)下载,或者使用已提供的安装包。安装过程:双击安装包,若电脑有 D 盘且无特殊需求,默认安装路径为 D:\phpstudy;若没有 D 盘,则需将路径......
  • DuckDB - Study Notes 4
         blockrangeindex,BRIN   adaptiveradixtree,ART Todownloadthenecessarydatasetforthisproject,pleasefollowtheseinstructions:1.Gotohttps://www.kaggle.com/datasets/mohamedbakhet/amazon-books-reviews.2.ClickontheDown......
  • DuckDB - Study Notes 3
        DataWrangling CREATEORREPLACETABLEweb_log_text(raw_textVARCHAR); COPYweb_log_textFROM'access.log'(DELIM''); SELECTregexp_extract(raw_text,'^[0-9\.]*')ASclient_ipFROMweb_log_textLIM......
  • @Notes:将整数转为excel对应的列标
    /**@Notes:将整数转为excel对应的列标@Functionint_to_chr@param$index@param$start@returnstring@Authorgxk@Date2024/10/16@Time17:53*/functionint_to_chr($index,$start=65){$str='';if($index>=26){weibow.com$les=$index%26;$index......
  • DDR Study - LPDDR4 BRC RBC QoS
    参考来源:LPDRAM4/4XPerformanceTweaksBankrotation/interleaving基于DRAM的BANK-ROW-COLUMN结构,以及访问同一Bank内不同Row时需要的Pre-Charge+Active行为,对两种Address组成进行分析:Bank-Row-ColumnAddressScheme(BRC)这种结构下,MSB是CS+BankGroup,LSB是Colu......
  • notes for linear algebra
    Diagonalization$1\le\dim(E_{\lambda})\le$multiplicityof\(\lambda\).Assume\(T\)splits,itisdiagonalizableiff$\dim(E_{\lambda})=$multiplicityof\(\lambda\)forall\(\lambda\).Cayley-HamiltonTheoremLemma1.Let\(W\)be......
  • Notes.js的安装和配置
    一、安装1、下载https://nodejs.org/en/download/2、安装  3、测试打开cmd查看node和npm版本node-vnpm-v 二、环境配置1、找到安装目录,新建node_cache和node_global文件夹 2、以管理员权限打开cmd输入npmconfigsetprefix"D:\nodejs\node_gl......
  • PHPstorm和PHPstudy配置运行教程
    先下载好PHPstudy以及PHPstorm解压安装1、打开PHPstudy,开启Apache服务创建站点(打开浏览器输入localhost,表示创建成功)2、打开phpstorm,未添加解释器浏览器运行输出则会显示502BadGateway3、添加解释器,点击右上角file,设置,点击PHP接着添加CLL解释器跳转至以下界面,添......
  • parquet & DuckDB
    Ingestandsplitdatainaflowhttps://docs.outerbounds.com/recsys-tutorial-L2/Givenourdatasetisinaparquetfile,inthislessonyouwilllearnhowtoleverageanopen-source,ahyper-performantdatabaseforanalyticsworkloadscalledDuckDB.You......