reading .sql file in talend

[sushantk19]

Hello,

I have a requirement to read .sql file in talend. .sql file consist of create table statement and insert into statement.

After reading some of the blogs, i have designed my job with the below design

tFileinputRaw_1--->main--->tFlowToIterate--->Iterate--->tDBinput.

Can you share if possible the basic and advanced settings of

tDBinput components. Till tFileinputRaw_1--->main--->tFlowToIterate it seems to be working fine without any errors.

how should i access the source table from .sql file say table X? what SQL query should we write in

tDBinput component?

[Anonymous]

To run a 'create or insert' SQL statement, you should use tDBRow component. tFileInputRaw read the file content as a string, I think you need to split the string to several sub-strings if the file contains several SQL statements, and then iterate each SQL statement one by one.

Can you show us an example of .sql file?

Regards

Shong

[sushantk19]

@shong: Please find the sample file as below:

-- MySQL dump 10.19 Distrib 10.3.35-MariaDB, for debian-linux-gnu (x86_64)

--

-- Host: database.internal  Database: lpn_db

-- ------------------------------------------------------

-- Server version 10.2.44-MariaDB-10.2.44+maria~stretch

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8mb4 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--

-- Table structure for table `user`

--

DROP TABLE IF EXISTS `user`;

/*!40101 SET @saved_cs_client   = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `user` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

 `roles` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,

 `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

 `last_seen` datetime NOT NULL COMMENT '(DC2Type:datetime_immutable)',

 `created_at` datetime NOT NULL COMMENT '(DC2Type:datetime_immutable)',

 `updated_at` datetime NOT NULL COMMENT '(DC2Type:datetime_immutable)',

 `has_given_performance_consent` tinyint(1) NOT NULL DEFAULT 0,

 `has_given_functional_consent` tinyint(1) NOT NULL DEFAULT 0,

 `is_deleted` tinyint(1) NOT NULL DEFAULT 0,

 `deleted_at` datetime DEFAULT NULL COMMENT '(DC2Type:datetime_immutable)',

 PRIMARY KEY (`id`),

 UNIQUE KEY `UNIQ_8D93D649E7927C74` (`email`)

) ENGINE=InnoDB AUTO_INCREMENT=1184 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `user`

--

LOCK TABLES `user` WRITE;

/*!40000 ALTER TABLE `user` DISABLE KEYS */;

INSERT INTO `user` VALUES (XXX);

/*!40000 ALTER TABLE `user` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-07-05 5:51:25

[sushantk19]

@Shicong Hong​ can you please share some sample screenshots how to execute a create/insert statement with tDBRow component for the above use case?

[InfoCraft]

Hi @Sushant Kapoor​ ,

The sql file script :

-- test.cars_01 definition

DROP TABLE IF EXISTS `cars_01`;

CREATE TABLE `cars_01` (

 `carID` varchar(15) NOT NULL,

 `designModel` varchar(30) DEFAULT NULL,

 `aClass` varchar(15) DEFAULT NULL,

 `brand` varchar(15) DEFAULT NULL,

 `rateWeekend` varchar(15) DEFAULT NULL,

 `rateWeekday` varchar(15) DEFAULT NULL,

 `securityGuarantee` varchar(15) DEFAULT NULL,

 PRIMARY KEY (`carID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO cars_01

(carID, designModel, aClass, brand, rateWeekend, rateWeekday, securityGuarantee)

VALUES('aa3', 'Audi A3', 'Middle', 'Audi', '250', '200', 'Full');

INSERT INTO test.cars_01

(carID, designModel, aClass, brand, rateWeekend, rateWeekday, securityGuarantee)

VALUES('aa4', 'Audi A4', 'Middle', 'Audi', '400', '300', 'Full');

INSERT INTO test.cars_01

(carID, designModel, aClass, brand, rateWeekend, rateWeekday, securityGuarantee)

VALUES('gs5', 'Golf S5', 'Middle', 'Volkswagen', '240', '155', 'Full');

INSERT INTO test.cars_01

(carID, designModel, aClass, brand, rateWeekend, rateWeekday, securityGuarantee)

VALUES('gs6', 'Golf S6', 'Middle', 'Volkswagen', '280', '190', 'Full');

INSERT INTO test.cars_01

(carID, designModel, aClass, brand, rateWeekend, rateWeekday, securityGuarantee)

VALUES('gs7', 'Golf S7', 'Middle', 'Volkswagen', '320', '270', 'Full');

INSERT INTO test.cars_01

(carID, designModel, aClass, brand, rateWeekend, rateWeekday, securityGuarantee)

VALUES('gs8', 'Golf S8', 'Premium', 'Volkswagen', '520', '430', 'Full');

INSERT INTO test.cars_01

(carID, designModel, aClass, brand, rateWeekend, rateWeekday, securityGuarantee)

VALUES('mc220', 'Mercedes AMG C63', 'Premium', 'Mercedes', '600', '480', 'Full');

INSERT INTO test.cars_01

(carID, designModel, aClass, brand, rateWeekend, rateWeekday, securityGuarantee)

VALUES('me600', 'Mercedes E600', 'Premium', 'Mercedes', '500', '300', 'Full');

INSERT INTO test.cars_01

(carID, designModel, aClass, brand, rateWeekend, rateWeekday, securityGuarantee)

VALUES('p2008', '2008 Blue HDI', 'Premium', 'Peugeot', '500', '380', 'Full');

INSERT INTO test.cars_01

(carID, designModel, aClass, brand, rateWeekend, rateWeekday, securityGuarantee)

VALUES('p206', '206+', 'Premium', 'Peugeot', '190', '100', 'Full');

INSERT INTO test.cars_01

(carID, designModel, aClass, brand, rateWeekend, rateWeekday, securityGuarantee)

VALUES('p308', '308 HDI', 'Premium', 'Peugeot', '400', '300', 'Full');

INSERT INTO test.cars_01

(carID, designModel, aClass, brand, rateWeekend, rateWeekday, securityGuarantee)

VALUES('rc3', 'Clio S3', 'Middle', 'Renault', '220', '130', 'Full');

INSERT INTO test.cars_01

(carID, designModel, aClass, brand, rateWeekend, rateWeekday, securityGuarantee)

VALUES('rc4', 'Clio S4', 'Middle', 'Renault', '320', '280', 'Full');

INSERT INTO test.cars_01

(carID, designModel, aClass, brand, rateWeekend, rateWeekday, securityGuarantee)

VALUES('rc5', 'Clio S5', 'Premium', 'Renault', '520', '430', 'Full');

;

[sushantk19]

@Covid19: i replicated your design, but i get the below error:

[statistics] connected

[FATAL] 15:16:48 lillydoo_dwh_qa.job_000_sqlsource_test_0_1.job_000_sqlsource_test- tDBInput_1 [Amazon](500310) Invalid operation: syntax error at or near "null".

My design is attached for your reference. do you know why there is triangular yellow symbol next to the upper flow attached? i havent defined any ports in this component. 

ALso, do you know if i need to create user table first with the data or directly use .sql file as my source. what are the settings in user component for lower pipeline. I have attached that screenshot also for your ref. please have a look why this job is not working.

[sushantk19]

@not specified not specified​ : Any update on above case?

[InfoCraft]

Hi @Sushant Kapoor​,

Your job is not like mine, you have to use tDBRow not tDBInput.

[sushantk19]

@not specified not specified​ :ok thanks. i updated my design as above ( replaced tDBInput component with tDBrow). but I still the same error:

[ERROR] 09:26:41 lillydoo_dwh_qa.job_000_sqlsource_test_0_1.job_000_sqlsource_test- tDBRow_1 - [Amazon](500310) Invalid operation: syntax error at or near "null" 

Please find the screenshot attached. could this be because of .sql file??

[InfoCraft · Accepted solution]

Hi @sushantk19,

sql syntax when inserting is not goodINSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

OR disable the line to continue the testI tested with your sql file and it works fine

if it still doesn't work with you, take screenshots of the component configuration (tFileInputRaw, tDBRow), and make sure the connection to the database is established.

Kind regards,

😷19.