This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
integration:rawdata:queries [2011/03/14 14:21] 127.0.0.1 external edit |
integration:rawdata:queries [2016/06/28 22:38] (current) |
||
---|---|---|---|
Line 11: | Line 11: | ||
[[integration:rawdata:updates|Updates]] | | [[integration:rawdata:updates|Updates]] | | ||
[[integration:rawdata:creation|Generating Data Files]] | | [[integration:rawdata:creation|Generating Data Files]] | | ||
- | [[integration:rawdata:implementation|Implementation Guides]] | + | [[integration:rawdata:integration|Integration Guides]] |
---- | ---- | ||
Line 18: | Line 18: | ||
Omnidex allows applications to issue SQL statements against raw data files using standard ODBC and JDBC interfaces. Once a raw data file has been included in an Omnidex Environment File, it is treated like a standard table. Applications can relate with this data file as a standard table, just as though the table was resident in a relational database. | Omnidex allows applications to issue SQL statements against raw data files using standard ODBC and JDBC interfaces. Once a raw data file has been included in an Omnidex Environment File, it is treated like a standard table. Applications can relate with this data file as a standard table, just as though the table was resident in a relational database. | ||
- | Applications can take advantage of the great breadth of the SQL language. Queries can filter rows using criteria, join tables, aggregate rows, order rows and process functions. | + | Applications can take advantage of the great breadth of the SQL language. Queries can filter rows using criteria, join tables, aggregate rows, order rows, and process functions. |
== Criteria == | == Criteria == | ||
Line 28: | Line 28: | ||
== Table Joins == | == Table Joins == | ||
- | Omnidex supports standard table joins between raw data files, including inner joins, outer joins and cross joins. Since raw data files do not generally have native indexing capabilities like relational databases, it is important to install Omnidex indexing on the join columns. This will insure the best optimization of table joins. | + | Omnidex supports standard table joins between raw data files, including inner joins, outer joins, and cross joins. Since raw data files do not generally have native indexing capabilities like relational databases, it is important to install Omnidex indexing on the join columns. This will insure the best optimization of table joins. |
== Aggregations == | == Aggregations == | ||
- | Omnidex supports standard aggregations such as COUNT, SUM, AVG, MIN and MAX, both with and without the GROUP BY clause. Omnidex indexing should be installed to achieve the best performance. | + | Omnidex supports standard aggregations such as COUNT, SUM, AVG, MIN, and MAX, both with and without the GROUP BY clause. Omnidex indexing should be installed to achieve the best performance. |
Aggregations are generally appropriate for binary datatypes. For delimited files, be sure to declare numeric columns using binary datatypes to allow aggregations. Of course, binary datatypes can only be declared when the numeric data is consistent and valid across all rows. | Aggregations are generally appropriate for binary datatypes. For delimited files, be sure to declare numeric columns using binary datatypes to allow aggregations. Of course, binary datatypes can only be declared when the numeric data is consistent and valid across all rows. | ||
Line 44: | Line 44: | ||
Omnidex allows a wide selection of [[dev:sql:functions:home|SQL Functions]] that can be used against raw data files. | Omnidex allows a wide selection of [[dev:sql:functions:home|SQL Functions]] that can be used against raw data files. | ||
- | Omnidex also allows [[admin:features:expressioncols|Expression-based Columns]] in a table declaration, which create a virtual column that returns the result of a SQL expression. | + | Omnidex also allows [[admin:features:expressioncols:home|Expression-based Columns]] in a table declaration, which create a virtual column that returns the result of a SQL expression. |
== Datatypes == | == Datatypes == | ||
- | Raw data files can support retrieving data in a wide variety of datatypes. The can store character and binary data, and data can be further converted in SQL statements. Raw data files do not support VARCHAR and CLOB datatypes due to their reliance on length variables; however, CHARACTER and STRING datatypes may be used instead with greater convenience. | + | Raw data files can support retrieving data in a wide variety of datatypes. They can store character and binary data, and data can be further converted using functions in SQL statements. Raw data files do not support VARCHAR and CLOB datatypes due to their reliance on length variables; however, CHARACTER and STRING datatypes may be used instead with greater convenience. |
==== ==== | ==== ==== |