In-database processing refers to using SQL directly inside the database to complete batch insertion, overwrite writes, and periodic scheduling. This capability is suitable for scenarios with short processing chains and where computation is close to the data. OceanBase AI Database provides the following in-database processing capabilities:
INSERT OVERWRITE SELECT: Overwrites data by partition or table, suitable for T+1 batch processing and full refresh scenarios.SUBMIT JOB: Submits large-scale processing tasks as asynchronous jobs, avoiding long-lived session keepalives.- Scheduled task scheduling: Orchestrate periodic processing tasks through the Event Scheduler.
Overwrite query results (INSERT OVERWRITE SELECT)
INSERT OVERWRITE SELECT is an operation that inserts query results into a target table while overwriting the existing data in that target table. It is primarily used in data cleansing and transformation processes to efficiently batch insert processed data into the database. This operation is atomic, which means that if any exception occurs during execution, the entire operation is rolled back.
Application scenarios
- When you need to batch import data and overwrite the target table,
INSERT OVERWRITE SELECTprovides a convenient approach. - It is suitable for data cleansing when the target table already contains data, and you want to replace the old data with new data.
- The source table can be an internal table or an external table.
Syntax example
INSERT [/*+PARALLEL(N)*/] OVERWRITE table_name [PARTITION (partition_name, ...)] [(list_of_columns)] select_stmt;
Considerations
- Use this operation with caution, as it deletes all data from the target table or partition. Ensure your business has backed up the data to prevent accidental loss.
- Ensure the data format returned by the
SELECTquery is consistent with the target table schema to avoid data insertion failures. - The difference between
INSERT INTO SELECTandINSERT OVERWRITE SELECTis thatINSERT INTO SELECTdoes not clear the data in the target table or partition before insertion; the processed data is incrementally written into the target table. - Do not write data to the same table simultaneously using both
INSERT OVERWRITE SELECTand real-time writing methods (INSERT INTO,REPLACE INTO,DELETE,UPDATE), otherwise the data written in real time will be discarded.
For more information about this operation, see Insert data.
Submit a job (Submit Job)
In OceanBase AI Database, the job submission feature allows users to submit and execute complex data processing tasks. These tasks can be batch processing jobs, ETL (Extract, Transform, Load) jobs, or other background data processing tasks. When importing data using INSERT INTO or INSERT OVERWRITE SELECT, the process is executed synchronously by default. When the data volume is large, the session may need to stay alive for a long time. Therefore, for scenarios involving large data volumes, it is recommended to submit data import tasks asynchronously.
Application scenarios
- Regularly perform complex data processing operations, such as batch data import, data cleansing, report generation, etc.
- Schedule large-scale data migration or transformation tasks.
Example of submitting a job
-- Submit Task
SUBMIT JOB INSERT OVERWRITE test
SELECT ex1.c1,t2.c2,sum(ex1.c3)
FROM ex1,t2
WHERE ex1.id = t2.id
GROUP BY ex1.c1,t2,c2;
-- Query the status of an asynchronous task
SHOW JOB STATUS WHERE job='job_id';
Jobs typically consist of multiple steps, such as data extraction, transformation, and loading, ensuring the entire data processing flow is executed automatically.
For details on syntax and behavior, see SUBMIT JOB.
Scheduled tasks
Scheduled tasks are used to periodically execute specified tasks according to a set schedule. With scheduled tasks, you can automate data processing workflows and reduce manual maintenance costs.
Application scenarios
- Regularly clean up expired data, generate reports, synchronize data, and so on.
- Automatically execute data processing tasks at specific times or for periodic tasks.
Example of creating a scheduled task
Use the Event Scheduler command to create a scheduled task.
-- Create Scheduled Task
CREATE EVENT myevent
ON SCHEDULE AT '2024-12-26 00:00:00' + INTERVAL 1 DAY
DO
INSERT INTO test SELECT * FROM ex_test WHERE date_key = curdate();
-- View Scheduled Tasks
SELECT * FROM information_schema.events WHERE event_name='myevent';