This topic describes how to replace Lightning with Hologres for data development.

Background information

Lightning is an interactive search service provided by MaxCompute. You can use Lightning to query data in MaxCompute projects based on PostgreSQL statements.

Lightning uses a shared cluster in which multiple users share resources. When multiple users concurrently query a large amount of data, the query performance may deteriorate and query requests may be queued up for resources. The queries sometimes are time-consuming. In addition, Lightning does not support queries by index. To resolve the preceding issues, you can use Hologres to replace Lightning.

Hologres is a real-time interactive analytics engine that is compatible with PostgreSQL. It provides the following benefits:
  • Supports resource segregation among users so that queries performed by different users do not affect each other.
  • Seamlessly integrates with the big data ecosystem and allows you to analyze and process petabytes of data with high concurrency and low latency.
  • Seamlessly integrates with MaxCompute at the underlying layer and allows you to use foreign tables to accelerate queries on MaxCompute data.
  • Allows you to use indexes to query MaxCompute data imported to Hologres and provides much better performance than other open source interactive search systems.
  • Provides the Java Database Connectivity (JDBC) or Open Database Connectivity (ODBC) driver that allows you to use business intelligence (BI) tools to analyze data from multiple dimensions.

Comparison between Hologres and Lightning

The following table compares Hologres and Lightning.
Category Item Hologres Lightning
Feature Supported syntax PostgreSQL 11 PostgreSQL 8.2
Data query based on internal tables Supported Not supported
Real-time data write Supported Not supported
Data query based on foreign tables Supported Supported
Parameter optimization Custom parameter adjustment Supported. For example, you can adjust database or user parameters based on your business requirements. Not supported
Number of development tools to which each instance is connected, which reflects the number of queries per second (QPS) Increases in a linear manner with the volume of available resources. Is fixed to 20.
Internal table Operations to create, modify, and delete tables by using DDL statements Supported Not supported
Data update Supported Not supported
Data write in real time Supported Not supported
Foreign table Loading of the Meta module Responds to queries in milliseconds. Consumes more time if the number of MaxCompute tables increases, which slows down queries.
Data type Supports the following data types:
  • Basic data types
  • DECIMAL 2.0
  • DATETIME
  • ARRAY
Supports only basic data types.
Performance Engine
  • Allows you to use foreign tables to accelerate data queries.
  • Allows you to use internal tables to query data, which provides much better performance than other open source interactive search systems.
N/A
Resource Supports resource segregation among users to provide stable query performance. Does not support resource segregation among users, which is prone to latency and performance deterioration during queries.

Procedure

The following figure shows the process of replacing Lightning with Hologres. Process
  1. Purchase a Hologres instance.
    To replace Lightning with Hologres, purchase a Hologres instance first. For more information, see Purchase a Hologres instance.
    Note Make sure that you activate the MaxCompute and Hologres services in the same region.
    After you purchase a Hologres instance, you can view information about the instance in the Hologres console.
  2. Create a database in the Hologres instance.
    After you purchase a Hologres instance, a database named postgres is automatically created. This database is allocated a few resources and used only for management purposes. You can create a database based on your business requirements.
  3. Connect to development tools.
    After you create a database in the Hologres instance, connect the instance to the development tools that are originally connected to Lightning. Replace the port number for connecting Lightning to development tools with the port number of the Hologres instance. Use the public endpoint of the Hologres instance.
    You can obtain the port number of the Hologres instance on the instance details page in the Hologres console.
    For more information about how to connect a Hologres instance to different development tools, see the following topics:
  4. Query data from MaxCompute.
    To use Hologres to query data from MaxCompute, create a foreign table in Hologres or import data from MaxCompute to Hologres. Perform the following steps:
  5. Optional:Grant permissions to a RAM user.
    Authorize a RAM user to use Hologres. For more information, see Grant permissions to a RAM user.
  6. Display data analysis results in a visualized manner.
    Replace the port number for connecting Lightning to BI tools with the port number of the Hologres instance. For more information about how to connect a Hologres instance to BI tools, see the following topics:
    Usage notes

    When you configure the connection to connect a Hologres instance to a BI tool, you must enter the information about the schema. When you use Lightning, enter the MaxCompute project name in the Schema field. After you replace Lightning with Hologres, enter the name of the schema in which the created foreign table resides.

    In this example, Quick BI is connected to the Hologres instance. The following figure shows the configuration of the connection to a data source in Quick BI. The following table describes the parameter settings when you use Lightning and when you replace Lightning with Hologres. Configure the connection to a data source
    Parameter Description when you use Lightning Description when you replace Lightning with Hologres
    Name The custom name of the connection. The custom name of the connection.
    Database Address The endpoint of Lightning. The endpoint of the Hologres instance. You can view the endpoint of the Hologres instance on the instance details page in the Hologres console.
    Port Number The port number for connecting Lightning to Quick BI. The port number of the Hologres instance. You can view the port number of the Hologres instance on the Configurations tab of the instance details page in the Hologres console.
    Database The name of the MaxCompute project. The name of the database to be connected in the Hologres instance. You can view the name of the database on the Database Authorization page in the Hologres console.
    Schema The name of the MaxCompute project. The name of the schema in the specified database. The default value is public. You can also specify a custom schema. After the connection is created, all the tables of the specified schema can be displayed. When you execute an SQL statement for ad hoc queries, you must specify the name of the table that you want to reference in a schema in the format of Schema name.Table name.
    Username The AccessKey ID of the account that you use to connect to the MaxCompute project. You can obtain the AccessKey ID from the Security Management page. The AccessKey ID of the account that you use to connect to the Hologres database. You can obtain the AccessKey ID from the Security Management page.
    Password The AccessKey secret of the account that you use to connect to the MaxCompute project. You can obtain the AccessKey secret from the Security Management page. The AccessKey secret of the account that you use to connect to the Hologres database. You can obtain the AccessKey secret from the Security Management page.