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