Creating a File Audit Task

Scenarios

You want to check whether SQL statements in a file comply with specifications and affect performance.

Constraints

• Only four types of syntax for nested statements can be audited. For details, see Table 1.
• The table name and table alias must be different. The aliases of different tables must be different.
• Tables in a view cannot be audited.
• Database system catalogs and views cannot be audited.
• MySQL statements containing number signs (#) cannot be audited.
• You cannot click Retry when the file is being or has been audited.
• If a system template is used, the audit results before and after a version upgrade will be inconsistent. You are advised to use a custom template.
• Up to 110 SQL audit tasks can be created, and up to 10 can be executed concurrently.

Procedure

1. Log in to the UGO console.
2. In the navigation pane, choose SQL Audit > Statement Audit. Click the SQL from Files tab.
3. Click Upload File.
   Figure 1 Upload a file
   

   Table 1 Parameter description

   Parameter	Example Value	Description
   Database Type	GaussDB	Select a database type. Currently, only GaussDB, MySQL, and PostgreSQL are supported.
   Data Source	app_db_01	Select a data source. Currently, only GaussDB, MySQL, and PostgreSQL are supported. This parameter is optional. If no data source is provided, the audit rules that depend on the data source are skipped by default.
   Schema	demo_schema_01	Select a schema. This parameter is optional and only available for GaussDB and PostgreSQL databases. If the SQL statement contains a schema name, the schema is used. If the SQL statement does not contain a schema name, the selected schema is used. If the SQL statement does not contain a schema name and no schema is selected, the public schema is used by default.
   Rule Template	baseline_template_01	Select a template based on the selected database type. You can set the template information by referring to Adding a Rule Template.
   Upload Data File	sql_code.zip	Upload a SQL file that meets the following requirements: The SQL object name can contain only lowercase letters. If you enter an uppercase SQL object name, the system automatically converts it to lowercase letters. The file can contain only simple SQL statements, such as INSERT, ALTER, DELETE, SELECT operations, and cannot contain stored procedures, functions, triggers, packages, or anonymous blocks, which is regarded as one SQL statement for audit. Only .zip, .xml, .sql, .java, and .json files can be uploaded. The file name can contain only digits, letters, underscores (_), and hyphens (-). Maximum file name length: 240 characters Max. file size: 5 MB. Only XML, SQL, Java, and JSON files in the .zip package can be audited. Any other file types will be skipped automatically. Max.size per file: 10 MB Max. files: 10,000 The file name can contain only digits, letters, underscores (_), and hyphens (-). Maximum file name length: 240 characters Files to be uploaded cannot contain sensitive data such as binary files, passwords, and keys. NOTE: Only JSON files generated during workload replay on DRS can be parsed.
   Recommend Index	Enable	This parameter is disabled by default.
   Description	Audit SQL statement for a service query	(Optional) Enter a description, which contains up to 100 characters.

4. Click OK to view the created task in the list.
   Figure 2 Audit task list
   

   The status can be one of the following:

   • Pending: The SQL audit task is to be scheduled.
   • Collecting objects: SQL statements scanned in the file.
   • Auditing SQL statements: The file is being audited.
   • Audit completed: The audit is complete only after all SQL statements in the file are audited.
   • Audit failed: An exception occurred during the audit.