Table of Contents
  • Cast & parse functions: Converting a byte array to a string or a string representation to an integer
  • Complex data types functions: Functions for working with the “bag” multiset data type
  • Datetime functions: Getting the time when a job was submitted, or calculating the number of seconds between two date-times
  • IP to location functions: Extracting the country name from a given IP address
  • Math functions: Calculating powers, logarithms, and trigonometric functions
  • Query functions: Executing a SQL query or updating a SQL database
  • String functions: Converting a string to lowercase, or removing leading and trailing whitespace

One of Integrate.io's most rewarding features is its ability to enact low-code and no-code digital transformation. Even with no experience in ETL or data integration, non-technical users can take advantage of Integrate.io’s intuitive drag-and-drop interface to build robust, complex data pipelines to a data warehouse or data lake in the cloud.

To make the platform even easier to use, Integrate.io has introduced the X-Console, a tool for testing your data transformation functions before you use them in production. In this article, we’ll go over everything you need to know about the Integrate.io X-Console: what it is, how it works, and how you can use it to debug the transformations in your data pipeline.

  1. What Is the Integrate.io X-Console?
  2. How Does the Integrate.io X-Console Work?
  3. Expression Editor Components
  4. Using the Integrate.io X-Console
  5. Conclusion

What Is the Integrate.io X-Console?

The Integrate.io platform is accessible for almost anyone, even those without a single scrap of programming knowledge. However, users with coding familiarity can enhance the platform’s capabilities in certain situations. Enter the Integrate.io X-Console. The X-Console is a tool for testing the functions in your data pipeline, so you can review your data processes as soon as you write them—before they go live.

You can think of the X-Console as akin to the terminal in Unix-based systems like Linux and macOS, or a REPL interpreter for programming languages like Python. The X-Console is a new environment unto itself, separate from the rest of your ETL system. Essentially, the X-Console is a safe staging ground for testing out the functions in your data integration pipeline, so you won't worry about breakage during your first-time deployment.

 

How Does the Integrate.io X-Console Work?

When you open up the Integrate.io dashboard, you see a visual representation of your ETL data pipeline.

thumbnail image

There are two ways you can access the X-Console from the Integrate.io dashboard: via a transformation component, or standalone.

To access the standalone X-Console, locate the visual representation of the pipeline in the lower righthand corner of your screen.

thumbnail image

Click on the <> button to access the standalone X-console.

thumbnail image

To access the X-Console from within a transformation component:

  1. Click on a transformation component in your data pipeline. This will launch a new modal window.
  2. In the “Expression” column, you should see a list of fields in that component. Click on the edit (pencil and paper) icon to the left of any of these fields to start the Expression Editor.
  3. The Expression Editor serves as “scrap paper” where you can draft and edit your expressions before you paste them into the X-Console. 

thumbnail image

To switch over to the X-Console, click on the “X-Console” button in the top right corner of the modal window.

thumbnail image

Expression Editor Components

On the left-hand side of the Expression Editor interface, you should see a component with three tabs: “Functions,” “Variables,” and “Fields.” By clicking on each tab, you can see the functionality available to you from within the X-Console. You can also get the full picture by browsing the Integrate.io documentation (“Integrate.io functions”, “System and pre-defined variables”).

Functions

Integrate.io comes equipped with more than 200 pre-built functions for building highly robust data pipelines. These functions include, but are not limited to:

Variables

The X-Console comes with three different variables: predefined, system, and user.

  • Predefined variables are assigned a value at runtime. For example, the _PACKAGE_LAST_SUCCESSFUL_JOB_SUBMISSION_TIMESTAMP variable keeps track of the last time that a job finished successfully.
  • System variables change the behavior of the system itself. For example, the _ADWORDS_API_MAX_INPUT_SPLITS variable defines the maximum number of concurrent requests to Google AdWords.
  • User variables are defined by the user for a given pipeline.

Fields

Finally, the X-Console also provides a list of the various fields in your current data schema. Because the schema may evolve as you move through the data pipeline, the contents of this tab depend on the current pipeline stage and component.

 

Using the Integrate.io X-Console

The X-Console should feel familiar if you’ve used languages with C-like syntaxes, such as Python or JavaScript (although technically the X-Console uses its own expression language). In this section, we’ll give a brief example of how to use the Integrate.io X-Console to debug your pipeline’s transformations.

Suppose that in the course of our data pipeline, we want to concatenate data from two unique sources. In Integrate.io, we do this using the CONCAT function, which operates over two or more strings. For example, the command

CONCAT(‘username’, ‘@example.com’)

will return the complete email address ‘username@example.com’.

Now if we want to test this functionality out for ourselves, we can use the X-Console to assign values to two variables str1 and str2, and then call the CONCAT function:

$ str1 = ‘hello’ $ str2 = ‘world’ $ CONCAT(str1, str2)

Note that we could have used the string literals ‘hello’ and ‘world’ themselves within the CONCAT function, without having to assign them to a variable.

If we run these three lines in the X-Console, we get the output:

==> ‘helloworld’

While this might be what we want, it’s more likely that we wanted a space between the words. Here, we can add a space to the beginning of str2 to get the desired output:

$ str2 = ‘ world’ $ CONCAT(str1, str2) ==> ‘hello world’

We can also use the X-Console to evaluate mathematical expressions, e.g.:

$ 1 + 2 ==> 3

We’ve already mentioned that the X-Console is a fresh environment and acts as a staging area for your code before you use it in production. As a result, this means that the data or variables you use at runtime will be unavailable—the X-Console only has access to the information you enter into it. For example, the _PACKAGE_LAST_SUCCESSFUL_JOB_SUBMISSION_TIMESTAMP variable needs access to the metadata about a particular job and a particular data flow, but this information is stored beyond the X-Console’s reach.

To get around this limitation, you can assign a dummy value to a variable, e.g.:

$ _PACKAGE_LAST_SUCCESSFUL_JOB_SUBMISSION_TIMESTAMP = 1613144611

where 1613144611 is a Unix timestamp corresponding to February 12, 2021, at 15:43:31 GMT. You can then use this variable within the X-Console.

Similarly, you can’t perform an operation such as

CONCAT(author, ‘ ‘, id)

because using the author and id fields would require access to the underlying data. Instead, you can substitute string literals in the expression as a quick sanity check:

CONCAT(‘John Smith’, ‘ ‘, ‘311433267’)

However, remember that closing the X-Console will delete any variables you had assigned. The environment works for quick testing of functions, not for long programming sessions.

Conclusion

 

The Integrate.io X-Console is just one reason why Integrate.io is the most powerful, user-friendly ETL platform on the market. With a rich expression language, a simple graphical user interface, more than 100 pre-built integrations, and top-notch technical support, Integrate.io allows anyone to become a data integration pro.

Ready to learn how Integrate.io can benefit your business? Get in touch with our team today for a chat about your business needs or to start your 14-day trial of the Integrate.io platform.