This topic outlines some best practices when working with ETLs to Extract-Transform-Load data.
Scheduling Best Practices
Conflicting Processes
When choosing a schedule for running ETLs, consider the timing of other processes, like automated backups, which could cause conflicts with running your ETL. For example, don't schedule automatic system updates for the same time windows when ETLs are expected to run. If you suspect that ETLs have attempted to run when their source database is offline, characteristic errors will result. See
below for details.
Cron Expressions
For precise timing of your ETL runs, consider using a cron expression. You can use a full cron expression to schedule ETLs. For example, you will find a builder for the Quartz cron format here:
http://www.cronmaker.com/See the
Quartz documentation for more examples.
Wrapper ETLs
In some scenarios you may want to be able to control the scheduling of the ETL from the user interface, or allow non-authors to control the scheduling, while keeping the ETL source under module version control. Achieve this by using a "wrapper" ETL defined in the user interface that calls module-based ETL tasks.
Learn about using an ETL to call another ETL in this topic:
ETLs Across Containers
A practical scenario for using an ETL is to have a secure location for sensitive data, then "expose" a selected subset of that data in another container for others to analyze and join with other less sensitive data. The basic guidelines for this scenario are as follows, where "AdminOnly" is the sensitive-data folder (only visible to admins) and "WorkingFolder" is the more accessible destination location where data will be analyzed by others.
1. Define an
external schema in the AdminOnly folder:
2. Where practical, write a
query in this AdminOnly folder to filter and join your data as you intend to expose it to others.
3. Make a
linked schema in the destination WorkingFolder 'exposing' the necessary parts of the source AdminOnly folder.
- Add a linked schema.
- Be sure that the query you wrote in step 2 is one of the tables exposed in your linked schema.
4. Write the
ETL in the WorkingFolder:
Restart of LabKey Server or the Database
Scheduled ETLs are designed to resume service automatically whenever the server or the database is restarted, either unintentionally, or as part of a planned upgrade. If you experience issues with previously schedule ETLs after restart, consult the logs below, and open a support ticket through your client support portal.
Any jobs in the WAITING or RUNNING state will be re-queued to run after the server restarts.
If an ETL goes from being
disabled to enabled, it will fire immediately. It will then fire again after the interval set by
the <interval> element.
All currently enabled ETLs check to see if they have work as soon as the server starts up (or they are directly queued if they don't have a 'modified since' strategy). For example, if an ETL has an interval set to 24 hours, and it ran at 3:00PM, but the server is restarted at 3:30PM, the ETL will check for work again right at startup, even though it's been less than 24 hours.
ETL Troubleshooting and Logging
Consult Logs
If you suspect a performance issue or if ETL jobs are not completing successfully, consult the following logs:
Primary Site Log:
- This log contains all logged output from LabKey Server.
- This log can be found at > Site > Admin Console > Settings and click View Primary Site Log File under Diagnostics.
All Site Errors Log:
- This log describes critical errors messages from the Primary Site Log.
- This log can be found at > Site > Admin Console > Settings and click View All Site Errors under Diagnostics.
Individual ETL Logs:
ETL All Job History:
- A history of all ETL jobs that have run across the entire server.
- For details see ETL: All Jobs History
- Identifying the specific time that the ETL job first failed can be helpful in locating the relevant log portions.
Initial Troubleshooting Checks
When troubleshooting a failed ETL or unexpected results, look for issues like:
- Mismatches in provided and expected data types: Look for an invalid 'date' value, for example.
- As another example, if an integer field contains a string value, you'll see an error like: ERROR: Failed to run transform from source. Caused by: java.sql.SQLException: Conversion failed when converting the varchar value '1.' to data type int.
- Filters or sorts on the source data may be returning unexpected results: Check that filters on the source and filters in the transformation do not conflict.
- New data has been added to the source data which is missing values for fields required by the ETL: Specifically, date values are often required but may be missing from new rows.
ETL Failure Notification Emails
Administrators can configure users to be notified by email in case of an ETL job failure by setting up pipeline job notifications.
Learn more in this topic:
Intermittent Source DB Connection Loss
If you see the below error messages (and their variants) on the
Data Integration page, they may be caused by a problem connecting to the source database. See the Interpreting Error logs section for more information on how to determine the root cause of the error.
These messages will not clear until the ETL runs again, i.e until it has more work to do. These messages will remain until there is work, or until the user Resets and Runs the ETL again.
This behavior is by design to let the user know the last/latest state it was in when it didn't have any work.
org.labkey.api.util.ConfigurationException: Problem with configured timestamp column mytable.date_time
org.labkey.api.query.QueryNotFoundException: Error on line 33: Query or table not found: mytable.myfield
org.labkey.api.util.ConfigurationException: Source schema not found: myschema
Interpreting Error Logs
When interpreting error logs, we recommend scrolling down to the bottom of the often long error messages provided where the root cause of the error is revealed. For example, that the source DB isn't available, or that DB is online but the source query/table itself doesn't exist. To find the specific reason for the error scroll through the "Caused by:" entries to the last one shown. In the example below, the root cause of the error is
Caused by: java.net.ConnectException: Connection timed out: connect.
ERROR TransformQuartzJobRunner 2018-08-31 00:49:24,569 QuartzScheduler_Worker-2 : Something went wrong while attempting to queue an ETL job {EHR_Module}/geriatrics
org.labkey.api.util.ConfigurationException: Problem with configured timestamp column 'q_geriatrics.DATE_TIME
at org.labkey.di.filters.ModifiedSinceFilterStrategy.initFilterAndGetTimestamps(ModifiedSinceFilterStrategy.java:246)
at org.labkey.di.filters.ModifiedSinceFilterStrategy.hasWork(ModifiedSinceFilterStrategy.java:155)
at org.labkey.di.pipeline.TransformTask.sourceHasWork(TransformTask.java:507)
at org.labkey.di.steps.SimpleQueryTransformStep.hasWork(SimpleQueryTransformStep.java:70)
at org.labkey.di.pipeline.TransformDescriptor.checkForWork(TransformDescriptor.java:332)
at org.labkey.di.pipeline.TransformDescriptor.checkForWork(TransformDescriptor.java:109)
at org.labkey.di.pipeline.TransformQuartzJobRunner.execute(TransformQuartzJobRunner.java:89)
at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)
Caused by: org.labkey.api.util.ConfigurationException: Can't create a database connection to org.apache.tomcat.dbcp.dbcp2.BasicDataSource@7f6a567d
at org.labkey.api.data.DbScope.getPooledConnection(DbScope.java:811)
at org.labkey.api.data.DbScope.getConnection(DbScope.java:632)
at org.labkey.api.data.JdbcCommand.getConnection(JdbcCommand.java:55)
at org.labkey.api.data.SqlExecutingSelector$ExecutingResultSetFactory.handleResultSet(SqlExecutingSelector.java:343)
at org.labkey.api.data.TableSelector.getAggregates(TableSelector.java:417)
at org.labkey.di.filters.ModifiedSinceFilterStrategy.initFilterAndGetTimestamps(ModifiedSinceFilterStrategy.java:238)
… 8 more
Caused by: java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:743)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:666)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:566)
at org.apache.tomcat.dbcp.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at org.apache.tomcat.dbcp.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:262)
at org.apache.tomcat.dbcp.pool2.impl.GenericObjectPool.create(GenericObjectPool.java:890)
at org.apache.tomcat.dbcp.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:432)
at org.apache.tomcat.dbcp.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:361)
at org.apache.tomcat.dbcp.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134)
at org.apache.tomcat.dbcp.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1543)
at org.labkey.api.data.DbScope.getPooledConnection(DbScope.java:807)
… 13 more
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:475)
at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:506)
at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:595)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:230)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
… 24 more
Caused by: java.net.ConnectException: Connection timed out: connect
at java.net.DualStackPlainSocketImpl.waitForConnect(Native Method)
at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:85)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at java.net.Socket.connect(Socket.java:589)
at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:161)
at oracle.net.nt.ConnOption.connect(ConnOption.java:159)
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:431)Related Topics
