This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision Next revision Both sides next revision | ||
|
admin:features:snapshots:creation [2012/03/30 21:29] doc |
admin:features:snapshots:creation [2012/03/30 21:36] doc |
||
|---|---|---|---|
| Line 23: | Line 23: | ||
| <code> | <code> | ||
| - | export (select * from INDIVIDUALS) to individuals.dat; | + | export (select * from INDIVIDUALS) to dat/individuals.dat; |
| - | + | ||
| - | export (select * | + | |
| - | from INDIVIDUALS) | + | |
| - | to individuals.dat; | + | |
| </code> | </code> | ||
| - | ... or it can use more complex statements that denormalize, aggregate, join or filter data. The following example creates a denormalized table that includes columns from many tables, as well as new columns generated from SQL expressions. | + | ... or it can use more complex statements that denormalize, aggregate, join or filter data. The following example creates a denormalized table from a subset of rows in several joined tables, including new columns generated from SQL expressions. |
| <code> | <code> | ||
| Line 91: | Line 87: | ||
| INDIVIDUALS.BIRTHDATE <= 'December 31, 1999' | INDIVIDUALS.BIRTHDATE <= 'December 31, 1999' | ||
| ) | ) | ||
| - | to simple_view.dat; | + | to dat/simple_view.dat; |
| </code> | </code> | ||
| - | === Using a Database or ETL Tool === | + | This process is repeated for all tables that will reside in the Omnidex Snapshot. Omnidex the tables are exported, a new Omnidex Environment File is created around these exported files. This Omnidex Environment is usually heavily indexed to provide a high-performing query environment. |
| - | When creating an Omnidex Snapshot using a database or ETL tool, simply request that the data be extracted into a raw data file or a delimited file. A raw data file is a binary stream of data, with fixed-length records using native datatypes. A delimited file uses delimiters to identify when one column ends and another begins, and similarly when one row ends and the next row begins. Refer to the section on [[integration:rawdata:home|Raw Data Files]] for detailed documentation. | + | <code> |
| + | create environment | ||
| + | in "simple_view.xml" | ||
| + | with delete; | ||
| + | create database "SIMPLE_VIEW" | ||
| + | type FILE | ||
| + | index_directory "idx" | ||
| + | in "simple_view.xml"; | ||
| - | One of the most common ways to create an Omnidex Snapshot is to expobe done using Omnidex's EXPORT command, or it can be done using any database tool that can generate a raw data file or a delimited file. | ||
| + | create table "COUNTRIES" | ||
| + | physical "dat/cnt.dat" | ||
| + | ( | ||
| + | "COUNTRY" CHARACTER(2) omnidex, | ||
| + | "DESCRIPTION" STRING(47) quicktext, | ||
| + | "LATITUDE" FLOAT omnidex usage "LATITUDE", | ||
| + | "LONGITUDE" FLOAT omnidex usage "LONGITUDE", | ||
| + | "CAPITAL" STRING(31) quicktext, | ||
| + | "CAPITAL_LAT" FLOAT omnidex usage "LATITUDE", | ||
| + | "CAPITAL_LONG" FLOAT omnidex usage "LONGITUDE", | ||
| + | constraint COUNTRIES_COUNTRY_PK primary ("COUNTRY") | ||
| + | ) | ||
| + | in "simple_view.xml"; | ||
| + | |||
| + | create table "STATES" | ||
| + | physical "dat/sta.dat" | ||
| + | ( | ||
| + | "STATE" CHARACTER(2) omnidex, | ||
| + | "DESCRIPTION" STRING(31) quicktext, | ||
| + | "STATE_CODE" CHARACTER(2) omnidex, | ||
| + | "REGION" CHARACTER(2) omnidex, | ||
| + | "COUNTRY" CHARACTER(2) omnidex, | ||
| + | "TAX_RATE" FLOAT omnidex, | ||
| + | constraint STATES_STATE_PK primary ("STATE"), | ||
| + | constraint STATES_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES" | ||
| + | ) | ||
| + | in "simple_view.xml"; | ||
| + | |||
| + | |||
| + | create table "GENDERS" | ||
| + | physical "dat/gdr.dat" | ||
| + | ( | ||
| + | "GENDER" CHARACTER(1) omnidex, | ||
| + | "DESCRIPTION" STRING(31) quicktext, | ||
| + | constraint GENDERS_GENDER_PK primary ("GENDER") | ||
| + | ) | ||
| + | in "simple_view.xml"; | ||
| + | |||
| + | |||
| + | create table "HOUSEHOLDS" | ||
| + | physical "dat/households.dat" | ||
| + | ( | ||
| + | "HOUSEHOLD" CHARACTER(12) omnidex, | ||
| + | "ADDRESS" CHARACTER(50) quicktext, | ||
| + | "CITY" CHARACTER(28) quicktext, | ||
| + | "STATE" CHARACTER(2) omnidex, | ||
| + | "ZIP" CHARACTER(5) omnidex, | ||
| + | "COUNTRY" CHARACTER(2) omnidex, | ||
| + | constraint HOUSEHOLDS_HOUSEHOLD_PK primary ("HOUSEHOLD"), | ||
| + | constraint HOUSEHOLDS_STATE_FK foreign ("STATE") references "STATES", | ||
| + | constraint HOUSEHOLDS_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES" | ||
| + | ) | ||
| + | in "simple_view.xml"; | ||
| + | |||
| + | |||
| + | create table "INDIVIDUALS" | ||
| + | physical "dat/individuals.dat" | ||
| + | ( | ||
| + | "INDIVIDUAL" CHARACTER(12) omnidex, | ||
| + | "HOUSEHOLD" CHARACTER(12) omnidex, | ||
| + | "NAME" CHARACTER(50) quicktext, | ||
| + | "GENDER" CHARACTER(1) omnidex bitmap, | ||
| + | "BIRTHDATE" ANSI DATE omnidex, | ||
| + | "PHONE" CHARACTER(14) omnidex, | ||
| + | "EMAIL" CHARACTER(60) quicktext, | ||
| + | constraint INDIVIDUALS_INDIVIDUAL_PK primary ("INDIVIDUAL"), | ||
| + | constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS", | ||
| + | constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS", | ||
| + | ) | ||
| + | in "simple_view.xml"; | ||
| + | |||
| + | |||
| + | create table "SIMPLE_VIEW" | ||
| + | physical "dat/simple_view.dat" | ||
| + | ( | ||
| + | "INDIVIDUAL" CHARACTER(12) omnidex, | ||
| + | "HOUSEHOLD" CHARACTER(12) omnidex, | ||
| + | "ADDRESS" CHARACTER(50) omnidex, | ||
| + | "CITY" CHARACTER(28) omnidex, | ||
| + | "STATE" CHARACTER(2) omnidex, | ||
| + | "REGION" CHARACTER(2) omnidex, | ||
| + | "TAX_RATE" FLOAT omnidex, | ||
| + | "ZIP" CHARACTER(5) omnidex, | ||
| + | "COUNTRY" CHARACTER(2) omnidex, | ||
| + | "NAME" CHARACTER(50) omnidex, | ||
| + | "GENDER" CHARACTER(1) omnidex bitmap, | ||
| + | "BIRTHDATE" ANSI DATE omnidex, | ||
| + | "AGE" INTEGER omnidex, | ||
| + | "AGE_GROUP" CHARACTER(10) omnidex bitmap, | ||
| + | "PHONE" CHARACTER(14) omnidex, | ||
| + | "PHONE_AREACODE" CHARACTER(3) omnidex, | ||
| + | "PHONE_PREFIX" CHARACTER(3) omnidex, | ||
| + | "PHONE_SUFFIX" CHARACTER(4) omnidex, | ||
| + | "EMAIL" CHARACTER(60) omnidex, | ||
| + | "EMAIL_MAILBOX" CHARACTER(60) omnidex, | ||
| + | "EMAIL_DOMAIN" CHARACTER(60) omnidex, | ||
| + | constraint VIEW_INDIVIDUAL_PK primary ("INDIVIDUAL"), | ||
| + | constraint VIEW_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS", | ||
| + | constraint VIEW_GENDER_FK foreign ("GENDER") references "GENDERS", | ||
| + | constraint VIEW_STATE_FK foreign ("STATE") references "STATES", | ||
| + | constraint VIEW_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES" | ||
| + | ) | ||
| + | in "simple_view.xml"; | ||
| + | </code> | ||
| + | |||
| + | This Omnidex Environment is now ready for indexes to be built and for queries to be run. At this point, it is the same as a standard, raw data file environment. Refer to the section on [[integration:rawdata:home|Raw Data Files]] for detailed documentation. | ||
| + | |||
| + | === Using a Database or ETL Tool === | ||
| + | |||
| + | When creating an Omnidex Snapshot using a database or ETL tool, simply request that the data be extracted into a raw data file or a delimited file. A raw data file is a binary stream of data, with fixed-length records using native datatypes. A delimited file uses delimiters to identify when one column ends and another begins, and similarly when one row ends and the next row begins. Refer to the section on [[integration:rawdata:home|Raw Data Files]] for detailed documentation. | ||