Ingesting data by using Spark

You can ingest data into IBM® watsonx.data through the web console. Ingestion through web console is supported only by using IBM Analytics Engine (Spark).

watsonx.data on Red Hat® OpenShift®

Before you begin

Add and register IBM Analytics Engine (Spark). See Adding a Spark engine.
For target table, user defined active Iceberg catalog connected to a running Presto engine is required. See Adding storage
Add remote storage for source data files. See Adding storage.
Add data sources for source data files. See Adding data source.
Optionally, you can create a schema in the catalog for the target table. See Creating schemas.
Optionally, you can also create a target table in the schema. See Creating tables.
To enable your Spark application and ingestion to work with the watsonx.data catalog and storage, you must have MetastoreAdmin, and DataAccess roles in the service access and Administrator role in the platform access, see Managing user access and Predefined roles and permissions in watsonx.data.

Procedure

Log in to the watsonx.data console.
From the navigation menu, select Data manager and click Ingest data.
Select one of the following storage options to proceed to the next page:
1. Local System: To select files from your local system.
2. Storages: To select remote files from your connected S3 compatible storage.
3. Databases: To select files from your connected databases.
If you selected Local system, complete the following steps.
1. Drag a file to the box or click to upload. Selected files are listed in the Selected file(s) section. Click Next.
  
  Note: You can add multiple files of same file type. The available file type options are CSV, Parquet, JSON, ORC, and AVRO. The maximum file size must be 500 MB.
2. In the Ingest data: Local page, you can see the details of the source files and upload more files if required.
  
  Note: You can remove individual files and can also remove all the files by using the Unselect all option.
3. Click the preview icon against the specific file you want to preview. This action will open a new File preview window displaying the tables of the selected file.
4. Click the Edit button to edit the column headers.
5. Modify the column headers as required to make any transformation.
6. You can use Cancel edit or Reset to revert the column headers to their original state, if you need to undo your changes.
7. For CSV files, you can select the Advanced attributes to customize the file interpretation for the following:
  - Header in first row: Select this option if the CSV file has a header row containing column names.
  - Column Delimiter: Specify the character used to separate columns in the CSV file.
  - File Encoding: Choose the character encoding used in the CSV file.
  - Row Delimiter: Specify the character used to separate rows in the CSV file.
  - Escape Character: Define the character used to escape special characters within the CSV file.
8. Click Save to save the changes.
9. In the Target table section, select the target catalog from the Select catalog list. The selected catalog must be active to perform an ingestion job.
10. Choose one of the schema options.
  1. Existing schema: To ingest source data into an existing schema. Search or select a target schema listed in the Select schema or enter new schema name drop-down.
  2. New schema: Enter a new schema name in the Select schema or enter new schema name field and explicitly click Create: <new schema name> to create a new schema from the source data.
11. Select the corresponding target table options based on the selection above.
  1. Existing table: To ingest source data into an existing table. Search or select a target table listed in the Select table or enter new table name drop-down. The existing table will be appended or replaced with the new ingested data. Default action being append.
  2. New table: Enter a new target table name in the Select table or enter new table name and explicitly click Create: <new table name> to create a new table from the source data.
12. If required, modify the auto-generated ingestion Job ID in the Job Details.
13. Select the IBM Analytics Engine (Spark) from the Select engine list. The registered Spark engines are listed here.
14. Select a pre-defined Job size from the options listed:
  1. Small
    Driver memory 2GB
    
    Driver cores 1 vCPU
    
    Number of executors 1
    
    Executor cores 1 vCPU
    
    Executor memory 2GB
  2. Medium:
    Driver memory 4GB
    
    Driver cores 2 vCPU
    
    Number of executors 2
    
    Executor cores 2 vCPU
    
    Executor memory 4GB
  3. Large:
    Driver memory 8GB
    
    Driver cores 4 vCPU
    
    Number of executors 4
    
    Executor cores 4 vCPU
    
    Executor memory 8GB
15. Click Done. The submitted ingestion job can be found in the Ingestion history tab of the Data manager page.
If you selected Storages, complete the following steps from the Ingest data: Storages page.
1. Select a storage bucket from the Select storage drop-down.
  
  Note: You can also add new storage bucket by clicking the Add + icon. For more information, see Adding storage. You can create a permanent storage connections accessible to all users based on permissions. in the infrastructure manager page by selecting Keep permanent on infrastructure manager. You can also create a temporary storage connections accessible to you for the time period during the ingestion by selecting Temporary Database. This temporary storage shall not be available in the infrastructure manager page nor can be accessed by other users.
2. Select the required file type based on the source data. The available file type options are CSV, Parquet, JSON, ORC, and AVRO.
3. Select the files to be ingested from the All files tab. Selected files are listed in the Files selected tab. You can see the details of the selected files in the File details section.
  
  Note: You can add multiple files of same file type. The maximum file size must be 500 MB.
  
  Note: You can remove individual files and can also remove all the files by using the Unselect all option.
4. Click the preview icon against the specific file you want to preview. This action will open a new File preview window displaying the tables of the selected file.
5. Click the Edit button to edit the column headers.
6. Modify the column headers as required to make any transformation.
7. You can use Cancel edit or Reset to revert the column headers to their original state, if you need to undo your changes.
8. For CSV files, you can select the Advanced attributes to customize the file interpretation for the following:
  - Header in first row: Select this option if the CSV file has a header row containing column names.
  - Column Delimiter: Specify the character used to separate columns in the CSV file.
  - File Encoding: Choose the character encoding used in the CSV file.
  - Row Delimiter: Specify the character used to separate rows in the CSV file.
  - Escape Character: Define the character used to escape special characters within the CSV file.
9. Click Save to save the changes.
10. In the Target table section, select the target catalog from the Select catalog list. The selected catalog must be active to perform an ingestion job.
11. Choose one of the schema options.
  1. Existing schema: To ingest source data into an existing schema. Search or select a target schema listed in the Select schema or enter new schema name drop-down.
  2. New schema: Enter a new schema name in the Select schema or enter new schema name field and explicitly click Create: <new schema name> to create a new schema from the source data.
12. Select the corresponding target table options based on the selection above.
  1. Existing table: To ingest source data into an existing table. Search or select a target table listed in the Select table or enter new table name drop-down. The existing table will be appended or replaced with the new ingested data. Default action being append.
  2. New table: Enter a new target table name in the Select table or enter new table name and explicitly click Create: <new table name> to create a new table from the source data.
13. If required, modify the auto-generated ingestion Job ID in the Job Details.
14. Select the IBM Analytics Engine (Spark) from the Select engine list. The registered Spark engines are listed here.
15. Select a pre-defined Job size from the options listed:
  1. Small
    Driver memory 2GB
    
    Driver cores 1 vCPU
    
    Number of executors 1
    
    Executor cores 1 vCPU
    
    Executor memory 2GB
  2. Medium:
    Driver memory 4GB
    
    Driver cores 2 vCPU
    
    Number of executors 2
    
    Executor cores 2 vCPU
    
    Executor memory 4GB
  3. Large:
    Driver memory 8GB
    
    Driver cores 4 vCPU
    
    Number of executors 4
    
    Executor cores 4 vCPU
    
    Executor memory 8GB
16. Click Done. The submitted ingestion job can be found in the Ingestion history tab of the Data manager page.
If you selected Databases, complete the following steps from the Ingest data: Databases page.
1. Select a database from the Select database drop-down.
  
  Note: You can also add new database by clicking the Add + icon. For more information, see Adding data source. You can create a permanent database connections accessible to all users based on permissions in the infrastructure manager page by selecting Keep permanent on infrastructure manager. You can also create a temporary database connections accessible to you for the time period during the ingestion by selecting Temporary Database. This temporary database shall not be available in the infrastructure manager page nor can be accessed by other users.
2. Select a schema from the Schemas window.
3. Select a table you want to ingest from the Browse Table section.
4. In the Target table section, select the target catalog from the Select catalog list. The selected catalog must be active to perform an ingestion job.
5. Choose one of the schema options.
  1. Existing schema: To ingest source data into an existing schema. Search or select a target schema listed in the Select schema or enter new schema name drop-down.
  2. New schema: Enter a new schema name in the Select schema or enter new schema name field and explicitly click Create: <new schema name> to create a new schema from the source data.
6. Select the corresponding target table options based on the selection above.
  1. Existing table: To ingest source data into an existing table. Search or select a target table listed in the Select table or enter new table name drop-down. The existing table will be appended or replaced with the new ingested data. Default action being append.
  2. New table: Enter a new target table name in the Select table or enter new table name and explicitly click Create: <new table name> to create a new table from the source data.
7. If required, modify the auto-generated ingestion Job ID in the Job Details.
8. Select the IBM Analytics Engine (Spark) from the Select engine list. The registered Spark engines are listed here.
9. Select a pre-defined Job size from the options listed:
  1. Small
    Driver memory 2GB
    
    Driver cores 1 vCPU
    
    Number of executors 1
    
    Executor cores 1 vCPU
    
    Executor memory 2GB
  2. Medium:
    Driver memory 4GB
    
    Driver cores 2 vCPU
    
    Number of executors 2
    
    Executor cores 2 vCPU
    
    Executor memory 4GB
  3. Large:
    Driver memory 8GB
    
    Driver cores 4 vCPU
    
    Number of executors 4
    
    Executor cores 4 vCPU
    
    Executor memory 8GB
10. Click Done. The submitted ingestion job can be found in the Ingestion history tab of the Data manager page.

Driver memory	2GB
Driver cores	1 vCPU
Number of executors	1
Executor cores	1 vCPU
Executor memory	2GB

Driver memory	4GB
Driver cores	2 vCPU
Number of executors	2
Executor cores	2 vCPU
Executor memory	4GB

Driver memory	8GB
Driver cores	4 vCPU
Number of executors	4
Executor cores	4 vCPU
Executor memory	8GB