• Products
    • Observe

      Keep tabs on your world in real-time, reducing a cacophony of activity from massive amounts of raw content into orderly, easy to consume data

    • Orient

      Our low-code, composable, distributed, and event-driven predictive analytics workflow engine mines for actionable insights hidden in data at any scale

    • Dominate

      This insight interaction and decision support application takes you beyond self-service, visual exploration of piles of insights & provides auto-ML powered actionable, scenario-specific advice to achieve the goals you specify

  • Markets
    • Government

      At BigBear.ai, we take pride in providing mission-critical services and solutions to our government customers. This includes Data Analytics, Systems Engineering, and Cyber services.

    • Commercial Markets

      BigBear.ai provides innovative AI solutions to companies across commercial markets, ranging from Space to Media to Shipping and Transportation.

  • Company
    Just Announced
    Julie Peffer and Josh Kinley
    Press Release
    BigBear.ai Announces Key Leadership Appointments in Finance and Corporate Development
    • Col – 1
      • About

        We help governments and businesses make the decisions that change markets and define outcomes

      • Investor Relations

        We operationalize artificial intelligence and machine learning at scale through our end-to-end platform

      • Partners

        We work with our technology partners to customize products for the private and public sectors

    • Col – 2
      • Team

        Our executive team brings decades of world-class experience

      • Newsroom

        News articles from our press room

  • Careers
    Apply Now
    Data Scientist/Analyst
    Location:

    San Diego, California

    Description:

    BigBear.ai an exciting opportunity for a Data Scientist/Analyst to join our team in San Diego. This position can be onsite in San Diego, CA office or remote.

    Category: Computer/Data Science

    • Col – 1
      • Explore Jobs
        • Returning Applicant Login
        • Current Employee Login
      • Culture
      • Benefits
      • Military and Veterans
      • Product and Technology
      • Join Our Talent Community

        Sign up to receive personalized alerts and stay up to date on job openings right for you

  • Resources
    Fostering AI Adoption - Dr. Reggie Brothers - GovCon DIFFERENT Podcast
    Dr. Reggie Brothers
    Video
    Fostering AI Adoption - Dr. Reggie Brothers - GovCon DIFFERENT Podcast
    • Col – 1
      • Blog

        See what’s new in artificial intelligence, machine learning, and data analytics

      • Resource Library

        Browse our resource library and discover more about our products and solutions

      • Newsroom

        News articles from our press room

  • Contact
BigBear.ai
  • Products
    • Observe

      Keep tabs on your world in real-time, reducing a cacophony of activity from massive amounts of raw content into orderly, easy to consume data

    • Orient

      Our low-code, composable, distributed, and event-driven predictive analytics workflow engine mines for actionable insights hidden in data at any scale

    • Dominate

      This insight interaction and decision support application takes you beyond self-service, visual exploration of piles of insights & provides auto-ML powered actionable, scenario-specific advice to achieve the goals you specify

  • Markets
    • Government

      At BigBear.ai, we take pride in providing mission-critical services and solutions to our government customers. This includes Data Analytics, Systems Engineering, and Cyber services.

    • Commercial Markets

      BigBear.ai provides innovative AI solutions to companies across commercial markets, ranging from Space to Media to Shipping and Transportation.

  • Company
    Just Announced
    Julie Peffer and Josh Kinley
    Press Release
    BigBear.ai Announces Key Leadership Appointments in Finance and Corporate Development
    • Col – 1
      • About

        We help governments and businesses make the decisions that change markets and define outcomes

      • Investor Relations

        We operationalize artificial intelligence and machine learning at scale through our end-to-end platform

      • Partners

        We work with our technology partners to customize products for the private and public sectors

    • Col – 2
      • Team

        Our executive team brings decades of world-class experience

      • Newsroom

        News articles from our press room

  • Careers
    Apply Now
    Data Scientist/Analyst
    Location:

    San Diego, California

    Description:

    BigBear.ai an exciting opportunity for a Data Scientist/Analyst to join our team in San Diego. This position can be onsite in San Diego, CA office or remote.

    Category: Computer/Data Science

    • Col – 1
      • Explore Jobs
        • Returning Applicant Login
        • Current Employee Login
      • Culture
      • Benefits
      • Military and Veterans
      • Product and Technology
      • Join Our Talent Community

        Sign up to receive personalized alerts and stay up to date on job openings right for you

  • Resources
    Fostering AI Adoption - Dr. Reggie Brothers - GovCon DIFFERENT Podcast
    Dr. Reggie Brothers
    Video
    Fostering AI Adoption - Dr. Reggie Brothers - GovCon DIFFERENT Podcast
    • Col – 1
      • Blog

        See what’s new in artificial intelligence, machine learning, and data analytics

      • Resource Library

        Browse our resource library and discover more about our products and solutions

      • Newsroom

        News articles from our press room

  • Contact
Home Blog Load Multiple json Files into a Database Table Using KNIME
Blog

Load Multiple json Files into a Database Table Using KNIME

Jim McHugh
November 18, 2019
  • Share
  • Share

I was recently tasked with loading a lot of historical json files into a database. I wanted to use KNIME to create a repeatable workflow to address this issue since this would be need to be an ongoing process after the initial loading of data was complete.

Here is the workflow I created (Figure 1).

Figure 1

Let’s take a look at the component steps for a better understanding of each node and why they are used. I started by reading a list of json files contained in a specified directory (Figure 2.). It is obvious that this is needed for the historical load but daily/weekly/monthly/quarterly/yearly loads can fail and you always want to ensure that all files are captured and processed.

Figure 2

Now that we have our list of files we will put this into a loop for processing. The first node we come to in the loop is the json Reader node (Figure 3.). We pass the file path to the json Reader node from the list we created in the previous step as a flow variable.

Figure 3

Here is where the fun begins. Now that we have read the json file into memory we need to break it into pieces so we can capture the data as columns for our database table. Step 1 is to break the json into columns (Figure 4.). Please note that you should prefix the json path with the header of the information you wish to load.

Figure 4

Next, we ungroup the columns (Figure 5.).

Figure 5

Now that we have the json rows broken out into columns we assign the json column to the database column (Figure 6.).

Figure 6

The output of this node process will contain only the rows of data to insert into your database table (Figure 7).

Figure 7

To ensure the success of the DB Insert node verify that all of the data type conversions are correct (Figure 8.).

Figure 8

The first file has been loaded into the database and the loop will continue until the list of files to be processed has been exhausted.

As you can see KNIME provides excellent ETL functionality that is easy to comprehend and use. I highly recommend this software.

BigBear.ai

2022 BigBear.ai • All Rights Reserved.

  • Privacy Policy
Corporate Headquarters

6811 Benjamin Franklin Drive, Suite 200
Columbia, MD 21046
Tel: 410.312.0885 • Email: [email protected]

CareersContact