HDFS Pipeline Scenario

Imagine that your organization has numerous applications running on-premises. These applications generate lengthy log files that contain possible errors the applications generate as they run.

Your goal: Get a running weekly and monthly count of the number of errors per application.

To accomplish this, you follow these steps sequentially. The steps are explained in detail in the sections below.

  1. Copy the first day’s application log files into HDFS.

  2. Run a Hadoop job that processes the log files and outputs the results to one output file.

  3. Use an HDFS pipeline to extract the results and import them into a SingleStoreDB table.

  4. Confirm that the SingleStoreDB table contains the data for the first day. If the data is correct, run steps one through three continuously, at the end of every day.


This scenario provides a hypothetical example of how you can use HDFS pipelines. It is a not a working example, as the application logs and the Hadoop job are not available.

Log File Format

Shown below is an example application log file containing selected errors from one day. The ... parts indicate lengthy sections containing errors that are removed for brevity.

[2019-03-01 09:30:08]: ERR-2030: File not found: file1.txt
[2019-03-01 12:15:35]: ERR-1010: Could not read configuration file conf_file.txt
[2019-03-01 14:00:10]: ERR-1520: Not enough memory available to open file file2.txt
[2019-03-01 16:40:35]: ERR-1010: Could not read configuration file conf_file10.txt
[2019-03-01 19:20:55]: ERR-1520: Not enough memory available to open file file15.txt

Your other applications generate log files that are formatted in a similar way. The errors contain the date and time of the error, the error code and the error message.

HDFS folder Setup

The HDFS folder /user/memsql/<application name> stores the input files, per application, that are processed by your Hadoop job. After your job runs, it outputs the resulting part-00000 file to the HDFS folder /user/memsql/output. The part-00000 filename has the current date appended to the end.

Running the Hadoop Job

Run the Hadoop job which extracts the error date and error code for each log entry. The job will write these fields, along with the application name, to a line in the output file.

An example output file is shown below.

App1, ERR-2030, 2019-03-01
App1, ERR-1010, 2019-03-01
App1, ERR-1520, 2019-03-01
App1, ERR-1010, 2019-03-01
App1, ERR-1520, 2019-03-01
App2, E-400, 2019-03-01
App2, E-250, 2019-03-01
App2, E-800, 2019-03-01
App2, E-400, 2019-03-01

Creating the Destination Table

Create the table where the data will be loaded from the pipeline.

CREATE TABLE app_errors (app_name TEXT, error_code TEXT, error_date DATE, SORT KEY (error_date));

The reason you use a columnstore table to store the application errors is because a columnstore is well suited for performing aggregate queries. Also, you will not be updating rows in the table once they are imported.

Creating Your Pipeline

Use the following statement to create a new pipeline named my_pipeline, where you reference the HDFS path /memsql/output/ as the data source, and import data into the app_errors table once the pipeline is started.

LOAD DATA HDFS 'hdfs://hadoop-namenode:8020/memsql/output/'
INTO TABLE app_errors

Should your CREATE PIPELINE statement fail, run SHOW WARNINGS. This command may provide information as to why the CREATE PIPELINE statement failed.

Testing Your Pipeline

After you run your CREATE PIPELINE statement successfully, run the tests in this section to confirm your pipeline is working.

The following query should return one row for each file that the pipeline has imported. In this case, the query will only return one row, since the Hadoop job generates one output file. For example, if you ran the job on January 1, 2019, FILE_NAME could have the value part-00000-20190101. The FILE_STATE field value should have the value Unloaded.

SELECT * FROM information_schema.pipelines_files WHERE pipeline_name = 'my_pipeline';

Run the following command to test if the master aggregator can connect to the HDFS namenode. If the test is successful, zero rows will be returned.

TEST PIPELINE my_pipeline LIMIT 0;

Run the following command to test if the SingleStoreDB leaf nodes can connect to the HDFS datanodes. If the test is successful, one row will be returned.

TEST PIPELINE my_pipeline LIMIT 1;

Starting Your Pipeline

Assuming the tests you ran in the previous section succeeded, start your pipeline in the foreground:


Starting your pipeline in the foreground allows you to see any errors, if they occur. After your pipelines ingests one batch of data into the app_errors table, your pipeline stops.

Run the following query, which should return one row with the part-00000-20190101 file. In this row, the FILE_STATE field should have the value Loaded, assuming no errors occurred when you started the pipeline.

SELECT * FROM information_schema.pipelines_files WHERE pipeline_name = 'my_pipeline';

Run SELECT * FROM app_errors; to view the records in the app_errors table. If your HDFS file /user/memsql/output/part-00000-20190101 contains the output as shown in the Running the Hadoop Job above, you will see nine records, each with an app_name, error_code and error_date field.

Syncing HDFS with Your Application Logs

Now that your Hadoop job generates an output file successfully and your pipeline imports the file successfully, you want to periodically copy your application log files to the /user/memsql/<application name> HDFS folders. To accomplish this, you write a script to automatically copy the files at the end of every day.

After you start the script, start your pipeline in the background to continuously ingest data into the app_errors table.

START PIPELINE my_pipeline;


Foreground pipelines and background pipelines have different intended uses and behave differently. For more information, see START PIPELINE.

Finding the Weekly and Monthly Error Count

Recall that your goal is to find a running weekly and monthly count of the number of errors each application generates. Prior to creating a weekly and monthly query that returns these counts, you write a simpler query that returns the number of errors per application and error code, for all records in the table.

SELECT COUNT(*), app_name, error_code FROM app_errors
GROUP BY app_name, error_code

Using the previous query as a starting point, you write the monthly query:

SELECT COUNT(*), app_name, error_code, MONTH(error_date) FROM app_errors
GROUP BY app_name, error_code, MONTH(error_date)

Finally, you write the weekly query, using WEEK(error_date, 2) to specify that the week begins on Sunday:

SELECT COUNT(*), app_name, error_code, WEEK(error_date, 2) FROM app_errors
GROUP BY app_name, error_code, WEEK(error_date, 2)

Using Kerberos Cache Support

The Kerberos cache file, also referred to as the credential cache or ticket cache, is a file that stores the Kerberos authentication tickets obtained by a client during the authentication process. This cache file is typically used by Kerberos clients to store and manage the client's credentials for future authentication requests.

SingleStoreDB supports using the Kerberos cache to reduce the number of requests to the Kerberos servers. By default, it creates the cache in /tmp/s2_krb5cc file, however, that can be changed by editing the pipeline configuration. Also, the ability to reuse Hadoop logins was implemented, which further reduces the amount of requests.

Begin by setting advanced_hdfs_pipelines to ON,  which is required to access Kerberized HDFS.

To enable reusing Hadoop logins, add the following lines to the CONFIG section JSON:

"allow_unknown_configs": true,
"kerberos.allow_login_reuse": true

It works best when all HDFS pipelines are using the same config.

Edit the CREATE PIPELINE syntax to modify the configuration for an existing pipeline. This will keep the information about already ingested files, unlike when creating a new pipeline.

Setting up caching is a little more complicated. Do the following:

  1. Install the Kerberos CLI client tools on all nodes.

  2. On each node, set up the /etc/krb5.conf file for the Kerberos server that is associated with the Hadoop cluster. The kinit -kt KEYTAB_FILE HADOOP_USER command should work with the keytab file and the Hadoop user used in a HDFS pipeline.

  3. Modify the pipeline config for it to include:

    "allow_unknown_configs": true,
    "kerberos.use_cache": true

Alternatively, a CREATE PIPELINE query may be used with the same definitions as in the initial CREATE but with a modified CONFIG section.

Some additional settings are available for Kerberos cache:

  • "kerberos.kinit_path": Can be used to set the path to the kinit executable, if it is not in $PATH.

  • "kerberos.cache_path": Path for the Kerberos cache, which is /tmp/s2_krb5cc by default.

  • "kerberos.cache_renewal_interval": Renewal interval for the cache, in seconds. The default interval is one hour. When the cache is older than this, new logins are performed via keytab.

Next Steps

See Load Data with Pipelines to learn more about how pipelines work.

Last modified: July 10, 2023

Was this article helpful?