Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision
Previous revision
admin:features:snapshots:creation [2011/01/10 21:45]
els created
admin:features:snapshots:creation [2016/06/28 22:38] (current)
Line 1: Line 1:
 +~~NOTOC~~
 +
 {{page>:​top_add&​nofooter&​noeditbtn}} {{page>:​top_add&​nofooter&​noeditbtn}}
  
Line 5: Line 7:
 ===== Omnidex Snapshots ===== ===== Omnidex Snapshots =====
  
-[[admin:​features:​snapshots:​home|Overview]] ​-> [[admin:​features:​snapshots:​benefits|Benefits]] ​-> **[[admin:​features:​snapshots:​creation|Creation]]** ​-> [[admin:​features:​snapshots:​portability|Portability]] ​-> [[admin:​features:​snapshots:​scalability|Scalability]]+[[admin:​features:​snapshots:​home|Overview]] ​
 +[[admin:​features:​snapshots:​benefits|Benefits]] ​
 +**[[admin:​features:​snapshots:​creation|Creation]]** ​[[admin:​features:​snapshots:​portability|Portability]] ​[[admin:​features:​snapshots:​scalability|Scalability]]
  
 ---- ----
Line 11: Line 15:
 ==== Creating Omnidex Snapshots ==== ==== Creating Omnidex Snapshots ====
  
 +Omnidex Snapshots are usually created by exporting data from an underlying database; however, they can also be created by combining data from any source. ​ Omnidex Snapshots can be created using the Omnidex EXPORT statement, or they can be create using any database or ETL tool that can create a raw data file or a delimited file.
 +
 +=== Using Omnidex'​s EXPORT Statement ===
 +
 +The process of creating an Omnidex Snapshot begins with creating an Omnidex Environment File that references the underlying relational database. ​ This will allow Omnidex to access the underlying database. ​ There is no need to add Omnidex indexing to this particular Omnidex Environment since it will only be used to export data.  Refer to the documentation for [[integration:​rdbms:​oracle:​home|Oracle]],​ [[integration:​rdbms:​sqlserver:​home|SQL Server]], or [[integration:​rdbms:​mysql:​home|MySQL]] for instructions to create this Omnidex Environment.
 +
 +Once Omnidex can access the underlying relational database, use the EXPORT statement to export data.  This can be as simple as exporting the entire table ...
 +
 +<​code>​
 +export (select * from INDIVIDUALS) to dat/​individuals.dat;​
 +</​code>​
 +
 +... 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>​
 +export ​       ​
 +  (
 +  select ​       INDIVIDUALS.INDIVIDUAL,​
 +                HOUSEHOLDS.HOUSEHOLD,​
 +                HOUSEHOLDS.ADDRESS,​
 +                HOUSEHOLDS.CITY,​
 +                HOUSEHOLDS.STATE,​
 +                STATES.REGION,​
 +                STATES.TAX_RATE,​
 +                HOUSEHOLDS.ZIP,​
 +                HOUSEHOLDS.COUNTRY,​
 +                INDIVIDUALS.NAME,​
 +                INDIVIDUALS.GENDER,​
 +                INDIVIDUALS.BIRTHDATE,​
 +                cast($compare_dates(INDIVIDUALS.BIRTHDATE,​ current_date,​ '​YY'​) ​
 +                  as integer) AGE,
 +                cast(case
 +                  when $compare_dates(INDIVIDUALS.BIRTHDATE,​ current_date,​ '​YY'​) ​
 +                    between 0 and 17 then '​0-17'​
 +                  when $compare_dates(INDIVIDUALS.BIRTHDATE,​ current_date,​ '​YY'​) ​
 +                    between 18 and 29 then '​18-29'​
 +                  when $compare_dates(INDIVIDUALS.BIRTHDATE,​ current_date,​ '​YY'​) ​
 +                    between 30 and 39 then '​30-39'​
 +                  when $compare_dates(INDIVIDUALS.BIRTHDATE,​ current_date,​ '​YY'​) ​
 +                    between 40 and 49 then '​40-49'​
 +                  when $compare_dates(INDIVIDUALS.BIRTHDATE,​ current_date,​ '​YY'​) ​
 +                    between 50 and 59 then '​50-59'​
 +                  else '​60+'​
 +                end as character(10)) AGE_GROUP,
 +                INDIVIDUALS.PHONE,​
 +                cast(substring(INDIVIDUALS.PHONE from 2 for 3) 
 +                  as character(3)) PHONE_AREACODE,​
 +                cast(substring(INDIVIDUALS.PHONE from 7 for 3) 
 +                  as character(3)) PHONE_PREFIX,​
 +                cast(substring(INDIVIDUALS.PHONE from 11 for 4) 
 +                  as character(4)) PHONE_SUFFIX,​
 +                INDIVIDUALS.EMAIL,​
 +                cast(case
 +                  when position('​@'​ in INDIVIDUALS.EMAIL) > 0 then 
 +                    substring(INDIVIDUALS.EMAIL from 1 for 
 +                      position('​@'​ in INDIVIDUALS.EMAIL) - 1)
 +                  else ''​
 +                end as character(60)) EMAIL_MAILBOX,​
 +                cast(case
 +                  when position('​@'​ in INDIVIDUALS.EMAIL) > 0 then 
 +                    substring(INDIVIDUALS.EMAIL ​
 +                      from position('​@'​ in INDIVIDUALS.EMAIL) + 1)
 +                  else ''​
 +                end as character(60)) EMAIL_DOMAIN
 +    from        INDIVIDUALS
 +    join        HOUSEHOLDS on INDIVIDUALS.HOUSEHOLD =
 +                HOUSEHOLDS.HOUSEHOLD
 +    join        STATES on HOUSEHOLDS.STATE = STATES.STATE
 +    join        COUNTRIES on STATES.COUNTRY = COUNTRIES.COUNTRY
 +    join        GENDERS on INDIVIDUALS.GENDER = GENDERS.GENDER
 +    where       ​HOUSEHOLDS.COUNTRY = '​US'​ and 
 +                INDIVIDUALS.BIRTHDATE <= '​December 31, 1999'
 +  )
 +  to          dat/​simple_view.dat;​
 +</​code>​
 +
 +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.
 +
 +<​code>​
 +create environment ​   ​
 + ​in ​                  "​simple_view.xml"​
 + ​with ​                ​delete;​
 +
 +
 +create database ​      "​SIMPLE_VIEW"​
 +  type                FILE
 +  index_directory ​    "​idx"​
 + ​in ​                  "​simple_view.xml";​
 +
 +
 +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.
  
  
 ====  ==== ====  ====
  
-**[[admin:​features:​snapshots:​benefits|Prev]]** | **[[admin:​features:​snapshots:​portability|Next]]**+**[[admin:​features:​snapshots:​home|Prev]]** |  
 +**[[admin:​features:​snapshots:​portability|Next]]**
  
 ====== Additional Resources ====== ====== Additional Resources ======
 
Back to top
admin/features/snapshots/creation.1294695923.txt.gz · Last modified: 2016/06/28 22:38 (external edit)