Skip to content

Instantly share code, notes, and snippets.

@tsoumdoa

tsoumdoa/0-load-bim-open-schema-parquet.sql

Last active July 28, 2025 14:22
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save tsoumdoa/0bb0340b617dfa42a3b8fce2dfdfbfba to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save tsoumdoa/0bb0340b617dfa42a3b8fce2dfdfbfba to your computer and use it in GitHub Desktop.
Download ZIP
Simple DuckDB query demo to work with Bim Open Schema by Ara 3D
Raw
0-load-bim-open-schema-parquet.sql
-- create table from parquet files:
CREATE TABLE Descriptors AS
SELECT * FROM read_parquet('./Descriptors.parquet');
CREATE TABLE Documents AS
SELECT * FROM read_parquet('./Documents.parquet');
CREATE TABLE DoubleParameters AS
SELECT * FROM read_parquet('./DoubleParameters.parquet');
CREATE TABLE Entities AS
SELECT * FROM read_parquet('./Entities.parquet');
CREATE TABLE EntityParameters AS
SELECT * FROM read_parquet('./EntityParameters.parquet');
CREATE TABLE IntegerParameters AS
SELECT * FROM read_parquet('./IntegerParameters.parquet');
CREATE TABLE PointParameters AS
SELECT * FROM read_parquet('./PointParameters.parquet');
CREATE TABLE Points AS
SELECT * FROM read_parquet('./Points.parquet');
CREATE TABLE Relations AS
SELECT * FROM read_parquet('./Relations.parquet');
CREATE TABLE StringParameters AS
SELECT * FROM read_parquet('./StringParameters.parquet');
CREATE TABLE Strings AS
SELECT * FROM read_parquet('./Strings.parquet');
Raw
1-add-index.sql
-- adding index (zero-based) to Documents, Entities, Points & Strings as those table don't have index.
-- Entities table
CREATE TABLE Entities_New AS
SELECT
*,
(row_number() OVER () - 1) AS "index"
FROM
Entities;
DROP TABLE Entities;
ALTER TABLE Entities_New RENAME TO Entities;
-- Descriptors table
CREATE TABLE Descriptors_New AS
SELECT
*,
(row_number() OVER () - 1) AS "index"
FROM
Descriptors;
DROP TABLE Descriptors;
ALTER TABLE Descriptors_New RENAME TO Descriptors;
-- Documents table
CREATE TABLE Documents_New AS
SELECT
*,
(row_number() OVER () - 1) AS "index"
FROM
Documents;
DROP TABLE Documents; --Rename Documents Table
ALTER TABLE Documents_New RENAME TO Documents;
-- Points table
CREATE TABLE Points_New AS
SELECT
*,
(row_number() OVER () - 1) AS "index"
FROM
Points;
DROP TABLE Points; --Rename Points Table
ALTER TABLE Points_New RENAME TO Points;
-- Strings table
CREATE TABLE Strings_New AS
SELECT
*,
(row_number() OVER () - 1) AS "index"
FROM
Strings;
DROP TABLE Strings; --Rename Strings Table
ALTER TABLE Strings_New RENAME TO Strings;
Raw
2-denorm-entities.sql
-- Denormalizing entities table by creating a view
CREATE OR REPLACE VIEW denorm_entities
AS
select LocalId, GlobalId,Entities."index" as name_index, s_name.String as name, s_category.String as category, s_path.String as path_name, s_title.String as project_name
from Entities
LEFT OUTER JOIN Strings AS s_name
ON Entities."name" = s_name."index"
LEFT OUTER JOIN Strings AS s_category
ON Entities.category = s_category."index"
LEFT OUTER JOIN Documents
ON Entities.Document = Documents."index"
LEFT OUTER JOIN Strings AS s_path
ON Documents.Path = s_path."index"
LEFT OUTER JOIN Strings AS s_title
ON Documents.Title = s_title."index";
select *
FROM denorm_entities;
Raw
3-number-of-entities-by-category.sql
SELECT
category,
COUNT(*) AS Occurrences
FROM
denorm_entities
GROUP BY
category
ORDER BY
Occurrences DESC;
@tsoumdoa
Copy link
Author

tsoumdoa commented Jul 28, 2025 •
edited
Loading

DuckDB + BIM Open Schema

This Gist is all about showing you how to explore your BIM data, exported using the awesome BIM Open Schema project by Ara 3D, with the super-fast and friendly DuckDB.

Think of this as a quick dive into getting started with querying your BIM data, whether you're a Revit user or just curious to see how it works!

What You'll Need (Getting Your Data Ready)

You've got a couple of easy ways to get your hands on some .parquet data:

  1. Export from Revit (for the adventurous!):
    If you're a Revit user and have the Bowerbird plugin installed, you can export your own data! Just run this command:
    Ara3D.Bowerbird.RevitSamples/CommandExportBIMOpenSchema.cs
    This will generate the .parquet files for you.

  2. Download a Sample (Quick & Easy!):
    Don't have Revit or just want to jump straight in? No problem! You can grab a pre-exported sample:
    Download and unzip snowdon.bimdata.parquet.zip
    Unzip this file, and you'll have your .parquet data ready to go!

Setting Up DuckDB

First things first, you'll need DuckDB itself. It's super easy to install:

Pro Tip: DuckDB UI for the Win!

While you can use the command line, I highly recommend checking out the DuckDB UI. It makes exploring your data and running queries much more visual and fun!

Important for UI users: Once you have your .parquet files, navigate your terminal to the directory where you've saved them, then simply run:

duckdb -ui

This will launch the UI in your browser, connected to your data's location!

What This Gist Shows You (The Fun Part!)

This Gist walks you through a really simple, but powerful, workflow:

  1. Creating Tables: We'll show you how to easily turn those .parquet files into queryable tables in DuckDB.
  2. Adding Indexes: Speed up your queries by adding indexes to your tables.
  3. Denormalizing Data: A quick peek at how you can "flatten" your data to make it easier to work with.
  4. Simple Queries: Get answers to questions like: "How many elements do I have for each category?"

This is just the tip of the iceberg! Once you've got this basic setup down, you can start building much more complex and insightful queries to truly understand your BIM data.

Happy querying! Let me know if you have any questions or cool ideas to share!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment