This is a demo dataset\datamodel for InterSystems IRIS. This model is build for the IRIS Dataset contest: https://community.intersystems.com/post/technology-bonuses-intersystems-iris-datasets-contest-2021
The data used by this datamodel are based on datasets from ourairports.com and openflights.org
To load the CSV files into the database the new LOAD DATA feature of IRIS Version 2021.2.0.617 (preview) is used. Details about the new SQL command can be found here: https://docs.intersystems.com/iris20212/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_loaddata
With this example project you have at least two use cases:
See and evaluate the using of LOAD DATA or just explore the openflights datamodel
To see this dataset in action with Apache Zeppelin please check this repo: https://github.com/andreas5588/openflights_demo
The data comes from ourairports.com and openflights.org.
The openflights.org website makes the data available under the Open Database License. For details please see: https://openflights.org/data.html#license The ourairports.com website makes the data from there available to the Public Domain. The data comes with no guarantee of accuracy or fitness for use.
The "dat"-files are simple text files
The "csv"-files are simple text files
You find the raw data files within docker container in folder: /opt/irisbuild/data for your own tests with the LOAD DATA command. The sql statements that are used to load the data for this project can be found in file https://github.com/andreas5588/openflights_dataset/blob/main//src/ddl/02_create_db_model.sql
The Tables are created in Namespace OPENFLIGHTS with SCHEMA dc_data_flights. The tables were named according to the files from which the data originated.
In the folder /doc/dbdoc you will find a complete html based documentation of the data model. The documentation contains details about table and column names as well as data types and relationships. To use the documentation, simply open the file /doc/dbdoc/index.html with a webbroser.
If the container is up and running then the documentation is available via the IRIS webserver: http://localhost:52773/csp/openflights/index.html
This is a screenshot of the entry page
From each entity you can navigate by relationships to the dependent tables. The relationships are also displayed graphically.
The generated documentation contains the explanations stored in the %DESCRIPTION property of tables and columns. With this information it should be easy to explore the data model.
To start working with this datamodel you have two options:
docker run --rm -d -p 1972:1972 -p 52773:52773 andreasschneiderixdbde/openflights-iris
Thats it! Now you can work with INterSystems IRIS and the Openflights-Database
git clone https://github.com/andreas5588/openflights_dataset.git
docker-compose up -d
Now that the container is running, you should be able to create a connection to the management portal or just create a database connection with your favorite sql query tool.
The JDBC URL should be looks like: jdbc:IRIS://localhost:1972/OPENFLIGHTS
You can use User: _SYSTEM with pwd: SYS
After this you should be able to query the OPENFLIGHTS namespace as you wish. Here are some example queries: https://github.com/andreas5588/openflights_dataset/blob/main//src/sql/dev_some_queries.sql
This is a article-post on community.intersystems.com related to this github repo: https://community.intersystems.com/post/tips-and-tricks-brand-new-load-data-command As described on this post, I had problems creating and populating the tables via DDL script in IRIS. Unfortunately $SYSTEM.SQL.Schema.ImportDDL does not support many SQL statements, e.g. USE DATABASE or LOAD DATA. Fortunately Benjamin De Boe (https://github.com/bdeboe) had a solution in one of his repositories :-) The IRIS class file /src/cls/Utils.cls and within the Openflights.Utils.RunDDL method based on his https://github.com/bdeboe/isc-adventureworks/blob/main/src/cls/AdventureWorks/Utils.cls file. I've just changed the statement delimiter from ";" to "GO". Thanks Benjamin!
While working with the data and developing this project, some scripts were created.
dev_add_fks.sql - based on the documentation the FKs are defined, but not all are currently there cause of data inconsistencies
dev_check_sql_diag.sql - with queries in this file the LOAD DATA state can be checked and analyzed
dev_drop_tables.sql - ... as the name says
dev_some_queries.sql - just some queries to work with the datamodel
The %Java Server enconding arg is now configured via irisi.script, so now after docker-compose build the Openflights Dataset is ready to use
docker handling improved, the tables are created and loaded into the docker image