topic Re: reading .sql file in talend in Talend Studio

reading .sql file in talend

sushantk19 — Fri, 15 Nov 2024 22:46:04 GMT

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?

Re: reading .sql file in talend

Anonymous — Wed, 06 Jul 2022 07:15:09 GMT

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

Re: reading .sql file in talend

sushantk19 — Thu, 07 Jul 2022 07:46:57 GMT

@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

Re: reading .sql file in talend

sushantk19 — Thu, 07 Jul 2022 09:05:39 GMT

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

Re: reading .sql file in talend

InfoCraft — Fri, 08 Jul 2022 12:39:52 GMT

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