This shows you the differences between two versions of the page.
|
integration:rawdata:creation [2011/04/04 21:34] deb |
integration:rawdata:creation [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ~~NOTOC~~ | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | |||
| - | ====== Integration: Raw Data Files ====== | ||
| - | |||
| - | [[integration:rawdata:home|Overview]] | | ||
| - | [[integration:rawdata:types|File Types]] | | ||
| - | [[integration:rawdata:environments|Environments]] | | ||
| - | [[integration:rawdata:queries|Queries]] | | ||
| - | [[integration:rawdata:updates|Updates]] | | ||
| - | **[[integration:rawdata:creation|Generating Data Files]]** | | ||
| - | [[integration:rawdata:implementation|Implementation Guides]] | ||
| - | |||
| - | ---- | ||
| - | ===== Generating Raw Data Files ===== | ||
| - | |||
| - | There are many ways to create raw data files. Omnidex is especially versatile at accessing data files from many different external sources. Omnidex can also be used to create raw data files from another data source using standard SQL statements. | ||
| - | |||
| - | ==== External Sources for Raw Data Files ==== | ||
| - | |||
| - | Raw data files are commonly generated from relational databases, third party tools, vendor subscriptions, and applications. | ||
| - | |||
| - | * **RDBMS Exports** - Relational databases and office products often have export utilities that create raw data files. These may be delimited files or fixed-length data files. | ||
| - | |||
| - | * **Third-party Tools** - Extract, transformation and loading (ETL) tools can produce a wide variety of delimited and fixed-length data file. | ||
| - | |||
| - | * **Vendor Subscriptions** - Vendors often distribute their data using delimited or fixed-length data files, either as one-time distributions or regular subscriptions. | ||
| - | |||
| - | * **Application** - Applications often generate log files that are valuable to index and search with Omnidex. | ||
| - | |||
| - | ==== Exporting Raw Data using Omnidex SQL ==== | ||
| - | |||
| - | Omnidex SQL statements can be used to generate different types of raw data files. The [[dev:sql:statements:export|EXPORT]] statement can be used to convert the results of any SQL statement into raw data files supported by Omnidex. These EXPORT statements can be as simple as exporting all of the columns from a table, or as complex as joining multiple tables with complex aggregations and expressions. | ||
| - | |||
| - | In the examples below, Omnidex EXPORT statements are used to create raw data files. These examples create fixed-length data files, but there are options to cause files to be created using different types. Consult the [[integration:rawdata:implementation|Implementation Guides]] to see instructions for exporting data to each type of file. | ||
| - | |||
| - | == Omnidex SQL Example 1: Export of a table == | ||
| - | |||
| - | This example exports all of the columns and rows from a table. | ||
| - | |||
| - | <code> | ||
| - | export (select * | ||
| - | from INDIVIDUALS) | ||
| - | to individuals.dat | ||
| - | with DELETE; | ||
| - | </code> | ||
| - | |||
| - | == Omnidex SQL Example 2: Denormalization of two tables == | ||
| - | |||
| - | This example joins two tables and exports specific columns, applying criteria to restrict the rows returned. It also uses SQL functions to create new columns. | ||
| - | |||
| - | <code> | ||
| - | export (select I.NAME, | ||
| - | H.ADDRESS, | ||
| - | H.CITY, | ||
| - | H.STATE, | ||
| - | H.ZIP, | ||
| - | I.PHONE, | ||
| - | I.EMAIL, | ||
| - | $compare_dates(I.BIRTHDAY, current_date, 'YEARS') AGE | ||
| - | from INDIVIDUALS I | ||
| - | join HOUSEHOLDS H on I.HOUSEHOLD = H.HOUSEHOLD | ||
| - | where H.STATE = 'CA') | ||
| - | to CA_individuals.dat | ||
| - | with DELETE; | ||
| - | </code> | ||
| - | |||
| - | == Omnidex SQL Example 3: Summarization of two tables == | ||
| - | |||
| - | This example joins two tables and exports aggregations to form a summary table. | ||
| - | |||
| - | <code> | ||
| - | export (select H.STATE, | ||
| - | H.ZIP, | ||
| - | count(distinct H.HOUSEHOLD), | ||
| - | count(*) | ||
| - | from INDIVIDUALS I | ||
| - | join HOUSEHOLDS H on I.HOUSEHOLD = H.HOUSEHOLD | ||
| - | group by H.STATE, | ||
| - | H.ZIP) | ||
| - | to individuals_summary.dat | ||
| - | with DELETE; | ||
| - | </code> | ||
| - | |||
| - | |||
| - | ===== ===== | ||
| - | |||
| - | **[[integration:rawdata:updates|Prev]]** | | ||
| - | **[[integration:rawdata:implementation|Next]]** | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | See also: | ||
| - | |||
| - | {{page>:integration:rawdata:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||