Infor Documentation

Topic URL copied to clipboard

  • Print this page
  • Print PDF
About this guide
Intended audience
Prerequisite knowledge
Related documents
Introduction to Data Fabric
Concepts
Data Lake
Data objects
Data object properties
What is included
Application
API suite
Homepage widgets
Compass JDBC driver
Quick guide to Data Fabric
Sending data to Data Lake
Viewing data in Data Lake
Extracting data from Data Lake
Querying data from Data Lake
Deleting data from Data Lake
Infor OS Service Limits
How to use the Data Fabric application
Security
Navigating to Data Fabric
Data Fabric Home page
Viewing Data Fabric version details
Bookmarking pages
Viewing data in Data Lake
Atlas
Searching by object name
Filtering by object type
Filtering the uncatalogued object names
Showing the absent objects
Sorting object names
Object name details
Refreshing the object names list
Browsing data objects
Viewing a data object
Searching for data objects
Filtering data objects
Refreshing data objects
Maximizing the Data Grid view
Maximizing the Object Content view
Showing UTC date time
Unit of measure
Personalizing columns
Exporting to CSV
Downloading data objects
Sending data to Data Lake
When to use each method
Using the bulk ingestion API
Data Preparation
Viewing your data
Using Infor ION
Extracting data from Data Lake
Compass
Data Objects and Views panel
Searching the list
Refreshing the list
Data object menu
View menu
Generating a SELECT statement
SELECT options
Syntax and formatting
Using the auto-suggest functionality
Formatting the query
Running a query
Running multiple queries
Results panel
Data results
Searching results
Altering the results view
Exporting to CSV
Error messages
Using Compass query APIs
Using the data object retrieval APIs
Compass JDBC Driver
Installation and configuration
Prerequisites for the Compass JDBC driver - version 2020-09
Downloading the Compass JDBC driver
Downloading and setting up the file location
The Infor Compass JDBC Driver.ionapi security credentials file
The Compass JDBC driver in your SQL query tool
Option 1 - Incorporating the security credentials using the .ionapi file in the same directory as the driver
Option 2 - Incorporating the security credentials in the connection string with the tenant ID
Option 3 - Incorporating the security credentials in the connection string without the tenant ID
Infor ION
Querying the Data Lake
Data object definitions
JSON data objects
DSV data objects
Date and datetime property definitions
Supported formats
Data object definitions for localized string values
Localized data definition, method 1
Localized data definition, method 2
Object metadata best practices for queryable data
Compass views
Creating a View
Updating a View
Dropping a View
Data Lake JDBC driver for Birst
Data Selection Features
Data Selection for Localized String Values
Troubleshooting SQL expressions
Data Lake Compass queries
Query functionality and syntax
Infor functionality
Queries for localized data
LOCALE keyword
Select localized values for a property
Data Catalog locale selections
Queries for incremental data loads
Using the LASTMODIFIED property
Use the INFOR.LASTMODIFIED() function in a WHERE clause for an incremental data load
Query functions for Data Lake and Compass properties
Data Lake properties
Query functions for Data Lake
INFOR.DATAOBJECTID
INFOR.DATAOBJECTSEQID
Compass properties
Query functions for Compass
INFOR.COMPASSTIMESTAMP
INFOR.PARTITION
Flexible data formats
INFOR.SHOW_VALUE
Infor value transformation functions
INFOR.CONCAT2 through INFOR.CONCAT6
INFOR.DATETIME_TO_TIMESTAMP
INFOR.TIMESTAMP_TO_STRING
INFOR.STRING_TO_TIMESTAMP
INFOR.SPLIT_VALUE
Query hints
includeAllVariations
includeDeletionsWithMaxVariations
includeInSelectAll
Parameters
skipReformatting
Variation handling
Select the maximum variation excluding deleted records
Select all variations of each record
infor.allvariations() table function
IncludeAllVariations query hint
Select the maximum variation including deleted records
infor.includedeleted () table function
IncludeDeletionsWithMaximumVariations query hint
Data Lake database schemas
Property definitions
String properties
Integer and numeric properties
Date and timestamp properties
Boolean properties
Query processing
Data administration stored procedures
Clear table with or without clearing data
INFOR.CLEAR_TABLE and clear Compass data
INFOR.CLEAR_TABLE and retain Compass data
INFOR.CLEAR_VIEW
Clear data
INFOR.CLEAR_DATA
INFOR.RESET_PARTITIONS
Query result set differences
SQL References
Query structure
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
UNION | UNION ALL
EXCEPT
INTERSECT
WITH
SELECT ALL
SELECT LIMIT number of rows
SELECT TOP number of rows
SELECT DISTINCT
SUBQUERIES
JOINS
Select individual properties from an object
Select individual properties using aliases from an object
Incorporate comments in a query
Select properties and include the Data Object load timestamp
Select records based on the last modified timestamp
Select all records
Select the Data Catalog Locale Selections
Select localized values for a property
Select localized values for a property and autofill unused property positions
Select localized values for a property that is not localized
Select a localized property without the locale function
Expressions and comparison functions
CASE
COALESCE
ISNULL
GREATEST
LEAST
NVL
NVL2
Logical and comparison operators
String functions
CHAR
CHARINDEX
CONCAT
CONCATENATION USING ||
INSTR
LEFT
LEN
LENGTH
LPAD
LOWER
LTRIM
REPLACE
RIGHT
RPAD
RTRIM
SUBSTR
SUBSTRING
TRIM
UPPER
Mathematical operators
Mathematical functions
ABS
CEIL
CEILING
FLOOR
INT
ISNUMERIC
POWER
ROUND
Signed expression
TRUNCATE
Aggregation functions
AVG
COUNT
MAX
MIN
SUM
Datetime functions
ADD_MONTHS
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEPART
DATE_FORMAT
DATE_SUB
DATE_TRUNC
DAY, DAYOFMONTH, DAY_OF_MONTH
FROM_UTC_TIMESTAMP
GETDATE
GETUTCDATE
LAST_DAY
MONTH
TO_DATE
TRUNC
UNIX_TIMESTAMP
YEAR
Conversion functions
CAST
CONVERT
CONVERT datetime to string
CONVERT string to datetime
Analytic functions
LEAD
RANK …OVER … PARTITION BY
ROW_NUMBER … OVER … PARTITION BY
Views
CREATE VIEW
ALTER VIEW
DROP VIEW
SELECT
Managing data in Data Lake
Marking or clearing data as corrupt
Deleting data from Data Lake
Deleting data objects in Atlas
Deleting data objects in Purge
Purging by object by ID
Purging by filter
Using the Delete APIs
Working with Purge logs
Exporting purge logs
Stopping a purge event
Purge Status Indicators
Managing Compass data
Data Lake query error handling
SQL Messages 200-399
Process Messages 400-499
Data Storage 500-599
Compass API Messages 700-749
Compass API Messages 750-799
Troubleshooting Compass queries
Initial query is executing for a long time
Updated Data Catalog data object metadata changes are not in Compass
Updated Data Catalog locale selections cause queries to fail with a 401 error
Incorrect query results when the results have duplicate column names
Queries fail after object metadata is updated
Limitations and restrictions for Views
Hints

Infor Data Fabric User Guide

  • Links
  • docs.infor.com
  • Send Feedback on Current Topic
  • Product Support
  • Open link in new tab
  • Open link in new window
  • Copy link to clipboard