LogoLogo
HomeCommunityLangstack.comCreate free account
  • 🤝Welcome
  • 🙌Support
  • 💡Get started
    • 📕Learn Langstack
      • Introduction to Langstack
        • Application templates
        • Storage frameworks
        • Account administration
      • Basics and essentials
        • What is an API gateway?
        • How to work with an API gateway
        • What is an Entity?
        • What is an Action box?
          • How to create and work with Action boxes
          • How to perform search in Actions
        • What is an Action?
          • How to create and work with Actions
          • Error propagation
          • Actions in Langstack applications
        • What is a Data type?
          • Simple Data types
        • What is a Variable?
          • Naming rules of a Variable
        • What is a Function?
          • Working with Functions
        • Navigating to a Variable or a Function
        • Implicit Type Conversion (ITC)
        • Explicit Type Conversion
        • Implicit and Explicit Type Conversion for Json
        • Next Steps
      • User management
        • What is a Privilege?
        • What are Groups?
        • What are Users?
      • Introduction to REST API
        • API Name
        • API Group name
        • Request and Response body
          • Request body
          • How to create and work with a Request body
          • Response body
          • How to create and work with a Response body
        • Query strings
          • How to create and work with Query strings
        • Headers
          • How to create and work with Headers
        • Path parameter
        • Create your first API: "Hello World!"
          • Working with API options
        • Exercise 1: GET REST API & Return Response Object
          • Step 1: Create API
          • Step 2: Test the API
        • Exercise 2: Delete a published API
        • Next Steps
      • Introduction to Entities
        • Fields
        • Records
        • Relations
        • Options for an Entity
        • Exercise 1: Customer Accounts and Activities
          • Customer accounts
            • Step 1: Create the Customers Entity
            • Step 2: Create the API
            • Step 3: Update entity through API
          • Customer Activities
            • Step 1: Create the Activities entity
            • Step 2: Create a “1 to Many” relation
            • Step 3: Create the API
            • Step 4: Update entity through API
        • Exercise 2: Customer Records
          • Step 1: Create the API
          • Step 2: Test the API
        • Next Steps
      • Programming with Entities
        • Create
        • Read
        • Update
        • Delete
        • JXPResponse
        • Next Steps
      • Using Triggers
        • After Create Trigger
        • After Update Trigger
        • After Delete Trigger
        • Exercise: Entity & Entity triggers
        • Step 1: Define “After Create” Trigger
        • Step 2: Define “After Update” Trigger
      • Connectors
        • Create a connector
        • SFTP Connector
        • MySQL Connector
        • Next Steps
      • Introduction to ETL pipeline
        • Create an ETL pipeline
        • Data Formats in ETL pipeline
          • Reader
          • Writer
          • Field mapping
        • Shared variables for ETL pipeline
        • ETL functions execution sequences overview
        • Displaying Functions
        • How to perform Search in ETL pipeline
        • Exercise 1: Skip Customer records
          • Step 1: Create the ETL pipeline
          • Step 2: Skip records based on Customer_ID
          • Step 3: Store skipped records in a list
          • Step 4: Store the Skipped Customer record
        • Exercise 2: ETL pipeline, CSV source & FTP Connector
        • Exercise 3: ETL pipeline, Database Source & MySQL Connector
        • Exercise 4: ETL pipeline, CSV Source & FTP Connector using Sections
          • Step 1: Create the ETL pipeline
          • Step 2: Reformat the Join_Date Values and define added fields
      • Multiple Executions, Time Duration, Linked App and Execution Status
        • Skip execution while in progress
        • Time duration settings
        • Linked App
        • Options for Delay Types
        • Execution status
      • Validation pipeline
        • Sequence of Validation pipeline
        • Exercise: Customer Information
          • Step 1: Create a Validation pipeline
          • Step 2: Create the API gateway
          • Step 3: Test the Validation pipeline
      • Introduction to Process
        • Create a process
        • Shared variables for a process
        • Exercise: Customer Anniversary Email Activity
          • Step 1: Check if the join date matches the current date
          • Step 2: Test the Process
      • Introduction to Flows & Flow components
        • What is a Flow?
        • What is a Flow component?
          • What is a Flow component Event?
        • Exercise 1: Customer Anniversary Email
          • Step 1: Create the Flow component
          • Step 2: Create the Flow
          • Step 3: Trigger the Flow
          • Step 4: Check records in the target entity
        • Exercise 2: Store list of customers
          • Step 1: Create the Flow component
          • Step 2: Create the Flow
          • Step 3: Create the API
    • 🌍Tour of Langstack (coming soon)
  • 📘In depth learning
    • 📖User Manual (coming soon)
    • 🔗Connectors
      • MS-SQL connector
        • Data type conversions between Langstack and MSSQL
        • Handling data loss in data type conversions
        • Setting up MS-SQL connector
        • MS-SQL connector as Reader and Writer
        • Using MS-SQL connector in ETL pipeline
          • MS-SQL connector as a source
          • MS-SQL connector as a destination
      • Google Drive connector
        • Set up Google Drive Connector
        • Adding Google Drive path in ETL pipeline Streams
        • Setting up GCP Account, GCP Project, API Enablement, and Credentials (Prerequisites)
          • OAuth 2.0 Client Credentials
          • Service Account Key Credentials
          • API Key Credentials
          • Authorization through Redirect URLs
        • Using Google Drive connector in ETL pipeline
          • Specifying file or folder paths
            • Direct file link
            • Folder link and file name
            • Folder path and file name
            • Folder path excluding file name
            • Folder path including file name
          • Google Drive connector as a source
          • Google Drive connector as a destination
      • AWS connector
        • Introduction
        • Set up AWS S3 Connector
          • OAuth 2.0 Client Type
          • Access Key
          • Public
        • Setting up AWS Account
          • Retrieve Client ID and Client Secret
        • Set up Amazon Web Services (Cognito) Console
          • Retrieve Identity Pool Id
          • Manage Permissions for Roles
          • Adding Redirect URL in Amazon Developer Console
          • User Consent for OAuth 2.0 Client through Login with Amazon
          • Retrieve Access Key Credentials
        • Creating a bucket in AWS
        • Using S3 connector in ETL pipeline
          • Specifying paths
          • Examples
            • AWS S3 connector as a source
            • AWS S3 connector as a destination
      • OneDrive connector
        • Set up OneDrive Connector
        • Setting up Microsoft Azure Account
        • Add URL to Authorized Redirect URLs
        • User Consent for Establishing OneDrive Connection
        • Usage of OneDrive Connector in ETL pipeline
          • Examples
          • OneDrive connector as a source
          • OneDrive connector as a destination
      • Dropbox connector
        • Set up Dropbox Connector
        • Setting up Dropbox Account
        • Add URL to Authorized Redirect URLs
        • User Consent for Establishing Dropbox Connection
        • Usage of Dropbox connector in ETL pipeline
          • Dropbox connector path settings
          • Examples
            • Dropbox connector as a source
            • Dropbox connector as destination
  • 🗃️Use Cases (Coming soon)
    • Use Case 1
    • Use Case 2
    • Use Case 3
Powered by GitBook
On this page
  • Requirements
  • Solution
  1. Get started
  2. Learn Langstack
  3. Introduction to ETL pipeline

Exercise 3: ETL pipeline, Database Source & MySQL Connector

In this exercise, store data in a MySQL database from Langstack Entity.

Requirements

  • Store the data through the ETL pipeline.

  • Source data is in a Langstack Entity.

  • The destination for the source data is a MySQL file. Use a MySQL tool as a connector. In this exercise, the tool in use is MySQL Workbench.

  • File must be residing on the MySQL connector before the execution of the ETL pipeline starts.

  • A source entity should be created beforehand. In this exercise, the Entity “UserName_Acc_customers” is used.

Solution

  • Open the MySQL tool.

  • Create a table with the column names exactly as field names in the Entity “UserName_Acc_customers” and name it “Exercise_Acc_customers”. Define the following columns:

    1. Customer_ID

    2. First_Name

    3. Last_Name

    4. IsActive

    5. Phone

    6. Email

    7. Join_Date

    8. Address

    9. City

    10. Zip_Code

    11. Country

    12. Customer_Value

    13. Rating

  • To go to the ETL pipeline template, click on “ETL pipeline” under the “Data management” menu on the left side panel.

  • To create a new ETL pipeline, click the [+ ETL pipeline] button under the “ETL pipeline” tab.

  • To label the ETL pipeline and execution log entity, add the following information:

    1. Enter the “ETL pipeline” name as “Username_Acc_Customers_2_MySQL”.

    2. Optionally add a description for this ETL pipeline.

    3. Enter a name for the execution log for this ETL pipeline as “entitytosql”. This log will be updated each time the ETL pipeline is executed and can be viewed by clicking “Go to records”.

  • To connect to the data source, add the necessary information needed in the “Data Source” section:

    1. Select the “Entity” tab.

    2. Select “UserName_Acc_customers” from the drop-down menu.

  • To connect to the data destination, add the necessary information in the “Data Destination” section:

    1. Select the “Connector” tab.

    2. In this exercise, “Tutorial_MySQL” is selected from the drop down menu. The connector can be added by selecting a connector from the drop-down menu or a new connector can be created by clicking the [+] button.

  • To disallow multiple requests simultaneously, leave the toggle button enabled for “skip execution while in progress”. Enabling this toggle button defines that the execution of this ETL pipeline will be skipped when there is one already in progress.

  • The default value for ETL pipeline execution is selected as “Immediate”. For this exercise, keep it as is.

  • To align the source fields with destination fields, click on the “Data Format” tab. In this tab, the settings for the reader and writer format are defined (based on the settings for the data source and the data destination). The “Reader” tab is selected by default. To add source fields, click the [+Field] button. Add all required fields.

  • All the added fields with their corresponding data types display as per the image below.

  • To update the settings for how the data from the source should be written to the target destination, select the “Writer” tab.

    1. Select writer stream as “Database Stream”.

    2. Click the “Edit the settings” arrow.

  • To add details necessary to write the records, define the settings in this section “MySQL format details”:

    1. Add Table name as “Exercise_Acc_Customers”. This is the name of the table that the ETL pipeline will write the data. This name must be exactly the same as the name for the destination table name.

    2. Add “Customer_ID” as the Unique Column name. In the Entity “UserName_Acc_customers”, “Customer_ID” is the unique field.

    3. Click the [Accept & Collapse] button. This will save the information related to the destination table/file.

  • To add and update the records, select the “Writer” mode as “Add & Update”. This mode defines that based on the match key specified, if a record is missing in the destination table and is present in the source Entity, the record will be added to the destination table and the data of the table will be updated.

  • To provide a criteria for the “Add & Update”, Click the [+ Match key] button.

  • Define a match key as follows:

    1. Select the Matched key as “Customer_ID”.

    2. Select the Writer sources as “Customer_ID”. This defines that based on the “Customer_ID” field, the records will be read from the Entity and written into the destination table. If a record is present in the source Entity and not present in the destination table, the record will be added and updated in the destination table.

  • To map the reader source fields to the entity destination fields, select the “Field Mapping” tab. To add the Mapped Fields, click the [+ Field] button and add relevant information.

  • To align the fields, add the following information:

    1. Select the “Reader” field in “Mapping Sources” by clicking the drop-down menu. e.g. in case of Customer ID, click on the “Mapping sources” drop-down menu, select Reader Fields>Customer_ID.

    2. Enter destination column to map the reader field to in Mapped to e.g. in case of Customer ID, enter “Customer_ID”. The destination table column name is exactly the same as the name entered in this field.

    3. Select data type e.g. in case of “Customer_ID”, select “GUID” (as this is the data type mentioned for “Customer_ID” in the Entity).

  • Add all the “Mapped Fields” in the exact same top-down position as specified in the “Reader” tab.

  • To save the ETL pipeline, click [Save].

  • To publish the ETL pipeline, click [Publish].

  • Ensure the ETL pipeline is enabled.

  • Click the [Run] button.

  • The Run ETL pipeline dialog box displays. Click [Run] to execute the ETL pipeline.

  • The ETL pipeline will be executed and the records will be added to the destination file.

  • To verify, go to MySQL Workbench and add the command to retrieve all records from the Entity. The column names will be displayed as per image below.

  • Run the command. The records from the Entity “UserName_Acc_customers” will be displayed.

PreviousExercise 2: ETL pipeline, CSV source & FTP ConnectorNextExercise 4: ETL pipeline, CSV Source & FTP Connector using Sections

Last updated 2 years ago

Click the “Edit the settings” arrow. In the settings, to add or update the details for the SQL Connector, update the required fields ().

💡
📕
Click here for the steps to create and update a connector