Talend Tutorial : Oracle to CSV with Custom Query

In this section i have Oracle Table and i want to use a custom query using aggregate function and store the result in csv file.

The job :

In the TOracleInput component, i defined the Attribute as follows, and in the Query i use "Select Gender, count(GENDER) FROM CHILDSTAT GROUP BY GENDER". The result will be 2 column, GENDER and count(GENDER) so we have to define a built in schema that contains 2 column. the first one is GENDER with string type and second column for storing count(GENDER) is integer type.

After that we have to define the component for OutputDelimited, the filename, row separator, and field separator.

the result is like this, GENDER will contain M for male and F for Female and the next column is the total of Male and Female.

Talend Tutorial : Create File Delimited in Talend

First, in the Repository menu select Metadata and it will show the sub like shown below. Right click on File Delimited then select Create File Delimited.

Enter the name, Purpose, and Description and then click next :

Next in the file click browse and find the file that you want. In this example i name my file pegawai.csv and it is located in D: and in the file viewer we can see the record of our file :

And then, we have to define the field separator, in this eample i use comma as field separator and enter as row separator. And in the Preview, i checked the Set heading row as column name because i defined the column name in the first row of the file.
After that we can see our record is properly served in table format and then click finish.

Membuat Koneksi R ke SQL SERVER

Buat bikin koneksi, kita pertama harus bikin koneksi dulu di ODBC data sources bro,

Jadi, disini udah ada koneksi ODBC sebelumnya yang namanya ODBCSQLServer, uid itu user id yang dipake pas setting ODBC nya bro, pwd juga password yang dipake sama user id yang lo bikin.

conn <- odbcConnect("ODBCSqlServer", uid = "sa", pwd = "Pokemon123321")

abis itu, kita coba bikin query buat ngambil data dari tabel di SQL Server bro, query nya disimpen di variabel data.

data <- "SELECT TOP 50 [CustomerKey], [FirstName], [LastName], [BirthDate], [MaritalStatus], [Gender], [EmailAddress], [yearlyIncome], [EnglishEducation], [EnglishOccupation]
FROM [AdventureWorksDW2014].[dbo].[DimCustomer]"

Terus, buat nampilin hasilnya pake perintah sqlQuery(connection, query) connectionnya disimpen di variabel conn, terus query nya disimpen di variabel data. query.result itu nama variabel buat nyimpen hasilnya bro.
odbcClose itu buat close koneksi,
dim(query.result) buat ngambil banyaknya kolom dan baris di dalam query.result ini.

query.result <- sqlQuery(conn, data)
odbcClose(conn)
dim(query.result)
query.result

Contoh Operator logika di R

Perhatikan, di bawah ini contoh operator logika di R. Dipake buat nentuin Bilangan yang di input itu ganjil atau genap.

Buat ngitungnya pake variabel modulus yaitu angka && 2, jadi kalo terus terusan dibagi 2 hasilnya 0, berarti itu bilangan genap, tapi kalo hasilnya bukan 0, berarti itu bilangan ganjil. 

Screenshot gambar pertama angka yang di input 10, jadi hasilnya Bilangan Genap :

Kalo pas di screenshot kedua ini angka yang dimasukin 7, jadi hasilnya Bilangan Ganjil :