Outputs

All of CSV, XLSX, PARQUET and SQLITE data can be produced with the following.

flatterer games.json games_dir --xlsx --csv --sqlite --parquet

This will create the directory structure:

games_dir/
├── csv
│   ├── games.csv
│   └── platforms.csv
├── parquet
│   ├── games.parquet
│   └── platforms.parquet
├── datapackage.json
├── fields.csv
├── tables.csv
├── sqlite.db
├── output.xlsx
├── postgresql
│   ├── postgresql_load.sql
│   └── postgresql_schema.sql
└── sqlite
    ├── sqlite_load.sql
    └── sqlite_schema.sql

CSV

The csv directory contains a CSV file representing a relational table. A new CSV file is produced for each one-to-many relationship found in the input JSON.

XLSX

The XLSX output can be found in the output.xlsx file. It contains the same data as the CSV files with a sheet per table. Sheet names are truncated to not go over the allowed limit.

SQLITE

A sqlite.db is created in the output directory where each csv file is put in its own table.

POSTGRES

Data is created in the postgres database where each csv file is put in its own table.

PARQUET

The parquet directory contains a parquet file representing a relational table.

fields.csv

fields.csv contains some metadata about the output tables:

table_name	field_name	field_type	count
platforms	_link	text	3
platforms	_link_games	text	3
platforms	name	text	3
games	_link	text	2
games	_link_games	text	2
games	id	number	2
games	rating_code	text	2
games	rating_name	text	2
games	releaseDate	date	2
games	title	text	2

• table_name: The name of the table, which will be the same as the CSV file name without the .csv extension, or the XLSX sheet name.

• field_name: The name of field in the table. This will be the same as the heading line in the CSV file and XLSX sheet.

• field_type: Type guess of the type of data within the field.

• count: Amount of times that field appears int the JSON.

tables.csv

tables.csv contains some metadata about the output tables:

table_name	table_title
platforms	platforms
games	platforms

• table_name: The name of the table, which will be the same as the CSV file name without the .csv extension, or the XLSX sheet name.

• table_title: This will be the same as table_name unless tables.csv input is used.

Postgresql Files

Need option sql_scripts=True. When using a CSV output a postgresql directory is made which contains SQL to help load the CSV files into the database.

postgresql_schema.sql

Contains SQL with the basic schema definitions for the tables created. The file looks like:

CREATE TABLE "games"(
    "_link" TEXT,
    "_link_games" TEXT,
    "id" NUMERIC,
    "title" TEXT,
    "releasedate" TIMESTAMP,
    "rating_code" TEXT,
    "rating_name" TEXT);

CREATE TABLE "platforms"(
    "_link" TEXT,
    "_link_games" TEXT,
    "name" TEXT);

Using the psql command line tool the schema can be loaded with something like:

psql postgresql://user:password@host/database -f games_dir/postgresql/postgresql_schema.sql

postgresql_load.sql

This script imports the CSV files into the tables created by postgresql_schema.csv. This script requires the psql command line tool as it uses its \copy command but can easily be adapted to use the plain COPY command by removing the \. The advantage of using \copy is that it does not have to be run on the server where the database is. It looks like:

\copy "games" from 'csv/games.csv' with CSV HEADER
\copy "platforms" from 'csv/platforms.csv' with CSV HEADER

and can be run by:

psql postgresql://user:password@host/database -f games_dir_/postgresql/postgresql_load.sql

Sqlite Files.

Need option sql_scripts=True. When using a CSV output a sqlite directory is made which contains SQL to help load the CSV files into a sqlite database.

sqlite_schema.sql

Contains SQL with the basic schema definitions for the tables created. The file is the same as the postgresql schema file.

Using the sqlite3 command line tool the schema can be loaded with something like:

cat games_dir/sqlite/sqlite_schema.sql | sqlite3 my_database.db

sqlite_load.sql

This script imports the CSV files into the tables created by sqlite_schema.csv. This script requires the sqlite3 command line tool as it uses its .import command. The contents looks like.

.mode csv 
.import 'csv/games.csv' games --skip 1 
.import 'csv/platforms.csv' platforms --skip 1 

and can be run by:

cat games_dir/sqlite/sqlite_load.sql | sqlite3 my_database.db

datapackage.json

Contains metadata in the Tabular Datapackge Spec. I looks like:

{
  "profile": "tabular-data-package",
  "resources": [
    {
      "profile": "tabular-data-resource",
      "name": "games",
      "schema": {
        "fields": [
          {
            "name": "_link",
            "type": "text",
            "count": 2
          },
          {
            "name": "_link_games",
            "type": "text",
            "count": 2
          },
          {
            "name": "id",
            "type": "number",
            "count": 2
          },
          {
            "name": "title",
            "type": "text",
            "count": 2
          },
          {
            "name": "releaseDate",
            "type": "date",
            "count": 2
          },
          {
            "name": "rating_code",
            "type": "text",
            "count": 2
          },
          {
            "name": "rating_name",
            "type": "text",
            "count": 2
          }
        ],
        "primaryKey": "_link"
      },
      "path": "csv/games.csv"
    },
    {
      "profile": "tabular-data-resource",
      "name": "platforms",
      "schema": {
        "fields": [
          {
            "name": "_link",
            "type": "text",
            "count": 3
          },
          {
            "name": "_link_games",
            "type": "text",
            "count": 3
          },
          {
            "name": "name",
            "type": "text",
            "count": 3
          }
        ],
        "primaryKey": "_link"
      },
      "path": "csv/platforms.csv"
    }
  ]
}