Talend: How to add StageModifiedDate and a CheckSum value in Text file Output

Consider you have a text file with User details, the text file doesn't have any data about created or modified date, in that case when you need to move this data to OLEDB database or to Data warehouse you may need stage modified date and Checksum value to improve the performance of the data load.

In this case, make sure you pass StageModifiedDate and CheckSum value to your staging tables, so those values helps you to perform incremental load to target main table.

To do this with Talend, design your job as like below:



In tMap, create a new variable 'stagemodifieddate' as shown below (make sure you use date format and output date pattern provide the required date format), this will add new output StageModifiedDate to schema.



To get CheckSum output, add tAddCRCRow component, and check all those columns that may undergo update, in below example I checked all. By doing so a new CRC column will be generated.



To verify tAddCRCRow, click on Edit Schema, you can see new column CRC


 Now, in text output you can add these two columns along with other data.

Talend: Unable to write Chineese Characters into Text files

When we load Chineese/Japaneese  characters from excel input to text file output using Talend, you may see those characters are replaced with '???'. To overcome this we need to select 'UTF-8' mode in Encoding option available in Advanced Settings.

I tried this in Talend Big Data Integration and worked well for me.

AWS Redshift: String contains invalid or unsupported UTF8 codepoints. Bad UTF8 hex sequence: b6

When you receive below error message from AWS redshift while executing use copy command then use 'ACCEPTINVCHARS ESCAPE' syntax in copy command:

Error Code: 'String contains invalid or unsupported UTF8 codepoints. Bad UTF8 hex sequence: b6 (error 3) '

COPY <tablename> from 's3://bucket/folder/file.txt'
CREDENTIALS '**********;aws_secret_access_key=******'
DELIMITER   '|' ACCEPTINVCHARS ESCAPE IGNOREHEADER 1;

General Copy Command to load S3 file data to Redshift

General Copy Command to load S3 file data to Redshift table:
COPY <tablename> from 's3://bucket/folder/file.txt'
CREDENTIALS 'aws_access_key_id=**********;aws_secret_access_key=*********'
DELIMITER   '|' IGNOREHEADER 1;

Creating ODBC Connection in Python

Creating ODBC Connection in Python

STEP 1: Installing pypyodbc can be done via the commandline:

           >>C:\Python34\Scripts>pip install pypyodbc

Using the below command in your code for better naming conventions:

           >> import pypyodbc as pyodbc

Python Script to Connect to SQL Server and Retrieve Data From a Table

# Python Script to Connect to SQL Server and Retrieve Data From a Table

import sys
import pypyodbc

# Create a new database:
connection_str =    """
                    Driver={SQL Server Native Client 11.0};
                    Server=localhost;
                    Database=master;
                    Trusted_Connection=yes;
                    """
db_connection = pypyodbc.connect(connection_str)
db_connection.autocommit = True
db_cursor = db_connection.cursor()
sql_command =   """
                CREATE DATABASE MYDB
                """
try:
    db_cursor.execute(sql_command)
except pypyodbc.ProgrammingError:
    print("Database 'MYDB' already exists.")
db_connection.autocommit = False

db_cursor.close()
del db_cursor
db_connection.close()

# Connect a database.
connection_str =    """
                    Driver={SQL Server Native Client 11.0};
                    Server=Localhost;
                    Database=MYDB;
                    Trusted_Connection=yes;
                    """
db_connection = pypyodbc.connect(connection_str)
db_connection.autocommit = True
db_connection.autocommit = False
db_cursor = db_connection.cursor()

# Select rows from a table:
# 1) Select all columns of all rows:
sql_command =   """
                SELECT *  FROM [ods].[MYTABLE]
                """
db_cursor.execute(sql_command)
rows = db_cursor.fetchall()
for row in rows:
      userid, fname= row[0] ,row[1]
  
      # Now print fetched result
      print (userid,fname)