Import SQL Server data

Import data

1. Log on to the Simple Log Service console.

2. In the Projects section, click the one you want.

   

3. On the Log Storage > Logstores tab, click the > icon to the left of the destination Logstore. Then, hover over Data Ingestion > Data Import and click +.

4. In the Quick Data Access dialog box, click SQL Server - Data Import.

5. Configure the import settings.

   1. In the Data Source Settings step, configure the following parameters.

      • If you use an RDS SQL Server database in a virtual private cloud (VPC), select Use VPC Address and RDS Instance. Then, configure the following parameters.

        Parameter	Description
        VPC Instance ID	The ID of the VPC where the RDS SQL Server database resides.
        RDS Instance ID	Set this parameter to the VpcCloudInstanceId of the RDS instance. You can call the DescribeDBInstanceAttribute operation to obtain the VpcCloudInstanceId. For more information, see Obtain the VpcCloudInstanceId of an RDS instance.

      • If you use a self-managed SQL Server database on an Elastic Compute Service (ECS) instance in a VPC, select Use VPC Address and Self-managed Database on ECS. Then, configure the following parameters.

        Parameter	Description
        VPC Instance ID	The ID of the VPC where the ECS instance resides.
        ECS Instance IP	The private IP address of the ECS instance.

      • If you use an RDS SQL Server database over the public network or a self-managed SQL Server database in other scenarios, deselect Use VPC Address. Then, configure the following parameters.

        Parameter	Description
        Public IP Address or Domain Name of Database	The public IP address or domain name of the SQL Server database.

      The following table describes the common parameters.

      Parameter	Description
      Configuration Name	The name of the configuration.
      MsSQL Port	The service port of the SQL Server database. The default value is 1433.
      Username	The account used to log on to the SQL Server database. We recommend that you grant only read permissions to this account.
      Password	The password for the account.
      Database	The name of the SQL Server database.
      Query Statement	You can use a SELECT or SHOW statement. If you use a SHOW statement, you cannot set the Page Size parameter in the advanced configuration. You can use a question mark (?) as a placeholder in conjunction with the CheckPoint Field. For example, if the query statement is SELECT * FROM employees WHERE id > ?, you can set CheckPoint Field to id and CheckPoint Field Start Value to 0. After each import task is complete, the system saves the ID of the last entry as a checkpoint. In the next task, the question mark (?) in the query statement is replaced with the ID value of this checkpoint.
      Advanced Configuration
      Maximum Rows per Import	SQL Server import tasks are scheduled. You can set the maximum number of rows for each import. The default value is 0, which means no limit.
      Connection Timeout	The timeout period for the import task to connect to the SQL Server database. The default value is 3000 ms.
      Query Timeout	The timeout period for the import task to query the SQL Server database. The default value is 5000 ms.
      Page Size	A value of 0 indicates that paging is disabled. If the value is greater than 0, the system appends OFFSET {checkpoint} ROW FETCH NEXT pageSize ROW ONLY to the SELECT statement to import data in pages within a single import task.
      Time Field	The field in the SQL Server data that represents time, such as create_time or update_time. Simple Log Service uses the value of this field as the import time for the data.
      Time Field Format	The format of the time field. Java time formats are supported. For example, yyyy-MM-dd HH:mm:ss. For more information, see Time formats.
      Time Field Time Zone	Select the time zone of the time field.
      CheckPoint Field	Select a checkpoint field as the starting point for the next data import. For example, if the query statement is select * from t where id > ?, where id is an auto-increment field, you can set id as the checkpoint field. You can also use fields such as create_time or update_time.
      CheckPoint Field Start Value	Set the start value of the checkpoint field.
      Additional Fields	Add additional fields in the map[string]string format. For example, if the query statement is select a, b from t;, the system imports two fields, a and b, by default. You can add other fields to import to Simple Log Service, such as {"k1": "v1", "k2": "v2"}.

   2. Click Preview to preview the import results.

   3. After you confirm that the settings are correct, click Next Configuration.

   4. On the Scheduling Interval tab, configure the following parameters.

      Parameter	Description
      Import Interval	The interval at which data is imported from the SQL Server database to Simple Log Service.
      Execute Immediately	If you enable Execute Immediately, an import task is run right away.

   5. After you complete the configuration, click Next.

6. Preview the data, create an index, and then click Next.

   By default, Simple Log Service enables full-text indexing. You can also manually create field indexes based on the collected logs, or click Auto Generate Indexes to have Simple Log Service automatically generate field indexes. For more information, see Create an index.

   Important

   To query and analyze logs, you must enable full-text indexing or field indexing. If you enable both, field indexes take precedence.

7. Click Query Logs to go to the query and analysis page and confirm that the SQL Server data was imported.

   Wait for about one minute. If data from the SQL Server database appears, the import is successful.

View the import configuration and statistical reports

After you create an import configuration, you can view the configuration and its statistical reports in the console.

1. Click the project.

2. In the navigation pane, choose Data Ingestion > Data Import, and then click the import configuration.

3. On the Import Configuration Overview page, view the basic information and statistical reports for the import configuration.

Related operations

On the Import Configuration Overview page, you can also perform the following operations.

• Modify the configuration

  Click Modify Configuration to change the import settings. For more information, see Import data.

• Delete the configuration

  Click Delete Configuration to delete the import configuration.

  Warning

  This operation cannot be undone. Proceed with caution.