Shulou(Shulou.com)06/01 Report--

Requirements:

Every time the business data or operation data is counted, it is sorted out and exported to excel documents, and the excel documents are regularly sent to the designated mailbox by email.

Environment: window system +kettle

Kettle build environment ignored.

File path configuration:

bat: bat file for storing timed tasks

excel: Excel file to store query results input

KJB: Operation file for storing kettle

KTR: conversion file for storing kettles

log: store log files (2 log files are stored here, can be split into 2 files again)

Configuration conversion:

1. Create a new conversion and configure DB connection to access the database.

In the core object's input group, drag table inputs and excel outputs in the output group.

2. After establishing the connection, start editing the table input and excel output. Output the results of the query to excel file.

Edit table input: note that the time field cannot use the time type and must be converted to a string, otherwise the output to excle will be empty.

Edit excel output: file & worksheet section, file name can contain date, if it is once a day, can contain date only.

Edit excel output: content section, get fields to see if the field type input from the table is the desired type.

Run the transformation to test, and save the file path to the previously predetermined configuration path.

Configuration jobs:

1. Select File New Job

In the General, Mail and File Management modules, select the module you want to use, as shown in the following figure.

The general idea of this example. Job Timed Scheduling Module---> Configured Conversion (confirm whether there is a problem with conversion, branch a sending email, and notify the email when conversion is abnormal)---> Conversion Output Result---> Send the result email to the recipient---> End Success ID

2. Configure each module content:

Start job scheduling module:

This is generally not adjusted, the default can be. Later, the timing task plan of the window system is used to execute the timing task of the kettle, which is more stable.

Conversion module:

The job name is selected according to the requirements. The conversion file is the file stored after the configuration conversion.

You can set the log: log level error log, as well as the name of the log file, to correspond to the log path. The others don't need to be set.

Configure Add to File Result File List Module:

Note here that check the result of the previous assignment as a parameter. Otherwise, the excel report produced by the previous conversion will not be sent to the recipient in the mail.

Finally, the complication is configuring the Send Mail module:

This is Netease mailbox, STMP server of Netease mailbox: smtp.163.com

Use your own 163.com email address as the email sender.

The mail server configuration is as follows:

Note that the verified password is the authorization code of your Netease mailbox. It is recommended to write some detailed information, such as the reply name circled in the figure below, to avoid being judged as spam by Netease mailbox.

When sending, if there is such an error, you need to pay attention to:

Run the job, make the conversion, and send the mail test:

You can also receive email alerts, which normally carry attachments:

At this point, basically a conversion job has been configured, and verification can also run normally and send mail.

Next, set the timed task schedule on the window.

Configure window timed task schedule:

1. Configure the executable task file bat.

Create a new notepad file, named chaxunshuchu.txt (whatever you want), with the following contents:

E:

cd E:\fq\kettle\pdi-ce-8.1.0.0-365\data-integration

kitchen.bat -file=E:\fq\kettle\KJB\query output excel table.kjb -level=basic>E:\fq\kettle\log\"%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%".log

The executable script above is interpreted as follows:

First enter the project disk

cd Go to kitchen.bat directory, under kettle installation directory

kitchen.bat asks to execute the command, followed by-file is the job file stored on the kettle.

-level is the level of logs generated, generally basic, Detailed, Debug, Rowlevel, Error, Nothing. Basic is generally used.

This is followed by the file path of the log entry:

"%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%" is time as log name, fixed format

.log is the suffix for logs.

Save the written Notepad file in ANSI format, which is recognized by the window system, and save the file type in.bat format.

2. Configure window timing execution plan

Open Computer Management and create a task schedule:

Create a new operation. The script here is the bat file written above:

Create a new trigger and set the task repetition. In this example, it is set to once every 10 minutes and executed indefinitely.

Finally, to make the final settings in the regular interface, note that the user logged in to the window system is used to perform the task, and the password needs to be entered for confirmation. When modifying the scheduled task later, the password of the user is also required for verification.

After setting, you can see the timed task situation:

Verification: The execution of timed tasks can be seen in the computer management, as follows, executed once at 14:44 and 14:54 respectively, as follows:

Check the email received as follows:

At this point, the entire configuration process is complete.

As long as this process is familiar, the report will be far away. Change the conversion at will. As long as the desired data is found and generated into the file, it will be sent to the designated mailbox through the scheduled task, and the daily report task will be completed.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.