C. Loading Data

We need to fire up DuckDB, in-memory or pointing to a file.

```{python}
%%sql

duckdb:///:memory:
-- duckdb:///path/to/file.db
```

And the data come in a SQLite file. DuckDB has a SQLite extension, so we just need to make sure it is installed and load it.

```{python}
%%sql

INSTALL sqlite;
LOAD sqlite;
```

And finally we just need to attach the SQLite file, which creates a bunch of views in the DuckDB side, for each table in the SQLite file.

```{python}
%%sql

CALL sqlite_attach('data/2022-12-28-ecas-export.db');
PRAGMA show_tables;
```

With everything loaded, we can simply query the tables. For example, let’s see the data about the meters.

%%sql 

SELECT * FROM meter;
_id name units comment vsf tsf cost fcost invert vmsetup type currency scaling phyunits bidir prod
0 1 [83, 116, 114, 111, 109] [] [] [53, 48] [53, 48] [] [] [48] <NA> [48] [36] [49, 46, 48] [] [48] [48]
1 2 [87, 195, 164, 114, 109, 101, 115, 116, 114, 1... [] [] [53, 48] [53, 48] [] [] [48] <NA> [48] [36] [49, 46, 48] [] [48] [48]
2 3 [87, 195, 164, 114, 109, 101, 115, 116, 114, 1... [] [] [53, 48] [53, 48] [] [] [48] <NA> [48] [36] [49, 46, 48] [] [48] [48]
3 4 [87, 97, 115, 115, 101, 114] [] [] [53, 48] [53, 48] [] [] [48] <NA> [48] [36] [49, 46, 48] [] [48] [48]
4 5 [76, 195, 188, 102, 116, 117, 110, 103, 115, 9... [] [] [53, 48] [53, 48] [] [] [48] <NA> [48] [36] [49, 46, 48] [] [48] [48]
5 6 [87, 97, 115, 99, 104, 109, 97, 115, 99, 104, ... [] [] [53, 48] [53, 48] [] [] [48] <NA> [48] [36] [49, 46, 48] [] [48] [48]
6 7 [71, 101, 115, 99, 104, 105, 114, 114, 115, 11... [] [] [53, 48] [53, 48] [] [] [48] <NA> [48] [36] [49, 46, 48] [] [48] [48]
7 8 [79, 102, 102, 101, 110] [] [] [53, 48] [53, 48] [] [] [48] <NA> [48] [36] [49, 46, 48] [] [48] [48]
8 9 [82, 101, 115, 101, 116] [] [] [53, 48] [53, 48] [] [] [48] <NA> [48] [36] [49, 46, 48] [] [48] [48]
9 10 [78, 97, 99, 104, 108, 97, 100, 101, 110] [] [] [53, 48] [53, 48] [] [] [48] <NA> [48] [36] [49, 46, 48] [] [48] [48]
10 11 [84, 101, 109, 112] [] [] [53, 48] [53, 48] [] [] [48] <NA> [48] [36] [49, 46, 48] [] [48] [48]
11 12 [87, 195, 164, 114, 109, 101, 114] [] [] [53, 48] [53, 48] [] [] [48] <NA> [48] [36] [49, 46, 48] [] [48] [48]

TODO: Temporarilly we will put this in a file to include. Later we will improve how to load and share the data across scripts.

Also, let’s not use sqlite_attach. Otherwise you would need a new session every time you want to attach a new file because it fails trying to attach again the same table names (the views already exist). And there does not seem to be a way of telling sqlite_attach to simply replace everything. So better to use sqlite_scan to query only what we need.