二 Superset通过API创建数据库连接和数据集

Superset通过API创建数据库连接和数据集

1 登录

Post 登录

http://localhost:8088/api/v1/security/login

Body raw(json)

{
    "password": "123456",
    "provider": "db",
    "refresh": true,
    "username": "admin"
}

Example

Request

POST /api/v1/security/login HTTP/1.1
Host: localhost:8088
Content-Length: 101

{
    "password": "123456",
    "provider": "db",
    "refresh": true,
    "username": "admin"
}

Response

{
    "access_token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJmcmVzaCI6dHJ1ZSwiaWF0IjoxNzExMDAyNjY3LCJqdGkiOiIyODc4MjZlNS02NTBmLTQwNTktYTEzMC0zNmYwNGIxODE5MWQiLCJ0eXBlIjoiYWNjZXNzIiwic3ViIjoxLCJuYmYiOjE3MTEwMDI2NjcsImNzcmYiOiJiNTEyYzc3Zi0zMjc2LTQyN2YtYjliYy05Nzc4NGE3MmVhN2QiLCJleHAiOjE3MTEwMDM1Njd9.MIxUUuOo3h_IGFOSO_kAhO1kQfAL6NQ8upXTLOEYaMY",
    "refresh_token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJmcmVzaCI6ZmFsc2UsImlhdCI6MTcxMTAwMjY2NywianRpIjoiMjFiNjVjMjgtMjBjYi00MjZmLWJlNTAtY2I2NDA2YzFmNGIxIiwidHlwZSI6InJlZnJlc2giLCJzdWIiOjEsIm5iZiI6MTcxMTAwMjY2NywiY3NyZiI6ImQ5ZDhjYTQzLTU3ZWUtNDc0ZC1hOTY5LWVhZTAyNTcyY2EzYiIsImV4cCI6MTcxMzU5NDY2N30.ynpSE9MQHodjxl_7QTcvKMIkIHgJXjxVElHDxeCZs4Y"
}

2 创建数据库连接

笔记

需要把登录接口返回的access_token设置到Authorization Bearer Token,用于权限验证

Post 新增数据源

http://localhost:8088/api/v1/database/

Authorization Bearer Token

Token <access_token>

Body raw(json)

{"engine":"mysql","configuration_method":"sqlalchemy_form","database_name":"MySQL6","sqlalchemy_uri":"mysql://root:embed@192.168.31.111:3306/superset"}

Example

Request

POST /api/v1/database/ HTTP/1.1
Host: localhost:8088
Content-Length: 151

{"engine":"mysql","configuration_method":"sqlalchemy_form","database_name":"MySQL6","sqlalchemy_uri":"mysql://root:embed@192.168.31.111:3306/superset"}

Response

{
    "id": 1,
    "result": {
        "configuration_method": "sqlalchemy_form",
        "database_name": "MySQL6",
        "driver": "mysqldb",
        "expose_in_sqllab": true,
        "parameters": {
            "database": "superset",
            "encryption": false,
            "host": "192.168.31.111",
            "password": "XXXXXXXXXX",
            "port": 3306,
            "query": {},
            "username": "root"
        },
        "sqlalchemy_uri": "mysql://root:XXXXXXXXXX@192.168.31.111:3306/superset",
        "uuid": "7d873883-0762-4bdc-8038-8ae35cd782b2"
    }
}

3 创建数据集

笔记

需要把创建数据库连接接口返回的id设置传递到databse参数

Post 新增数据集

http://localhost:8088/api/v1/dataset/

Authorization Bearer Token

Token <access_token>

Body raw(json)

{
  "database": 1,
  "schema": "bj_ld",
  "table_name": "222"
}

Example

Request

POST /api/v1/dataset/ HTTP/1.1
Host: localhost:8088
Content-Length: 67

{
  "database": 1,
  "schema": "bj_ld",
  "table_name": "222"
}

Response

{
    "data": {
        "always_filter_main_dttm": false,
        "cache_timeout": null,
        "column_formats": {},
        "columns": [
            {
                "advanced_data_type": null,
                "certification_details": null,
                "certified_by": null,
                "column_name": "name",
                "description": null,
                "expression": null,
                "filterable": true,
                "groupby": true,
                "id": 1,
                "is_certified": false,
                "is_dttm": false,
                "python_date_format": null,
                "type": "VARCHAR(255)",
                "type_generic": 1,
                "verbose_name": null,
                "warning_markdown": null
            },
            {
                "advanced_data_type": null,
                "certification_details": null,
                "certified_by": null,
                "column_name": "type",
                "description": null,
                "expression": null,
                "filterable": true,
                "groupby": true,
                "id": 2,
                "is_certified": false,
                "is_dttm": false,
                "python_date_format": null,
                "type": "VARCHAR(255)",
                "type_generic": 1,
                "verbose_name": null,
                "warning_markdown": null
            },
            {
                "advanced_data_type": null,
                "certification_details": null,
                "certified_by": null,
                "column_name": "length",
                "description": null,
                "expression": null,
                "filterable": true,
                "groupby": true,
                "id": 3,
                "is_certified": false,
                "is_dttm": false,
                "python_date_format": null,
                "type": "DOUBLE",
                "type_generic": 0,
                "verbose_name": null,
                "warning_markdown": null
            }
        ],
        "currency_formats": {},
        "database": {
            "allows_cost_estimate": false,
            "allows_subquery": true,
            "allows_virtual_table_explore": true,
            "backend": "mysql",
            "configuration_method": "sqlalchemy_form",
            "disable_data_preview": false,
            "engine_information": {
                "disable_ssh_tunneling": false,
                "supports_file_upload": true
            },
            "explore_database_id": 1,
            "id": 1,
            "name": "MySQL6",
            "parameters": {
                "database": "superset",
                "encryption": false,
                "host": "192.168.31.111",
                "password": "XXXXXXXXXX",
                "port": 3306,
                "query": {},
                "username": "root"
            },
            "parameters_schema": {
                "properties": {
                    "database": {
                        "description": "Database name",
                        "type": "string"
                    },
                    "encryption": {
                        "description": "Use an encrypted connection to the database",
                        "type": "boolean"
                    },
                    "host": {
                        "description": "Hostname or IP address",
                        "type": "string"
                    },
                    "password": {
                        "description": "Password",
                        "nullable": true,
                        "type": "string"
                    },
                    "port": {
                        "description": "Database port",
                        "maximum": 65536,
                        "minimum": 0,
                        "type": "integer"
                    },
                    "query": {
                        "additionalProperties": {},
                        "description": "Additional parameters",
                        "type": "object"
                    },
                    "ssh": {
                        "description": "Use an ssh tunnel connection to the database",
                        "type": "boolean"
                    },
                    "username": {
                        "description": "Username",
                        "nullable": true,
                        "type": "string"
                    }
                },
                "required": [
                    "database",
                    "host",
                    "port",
                    "username"
                ],
                "type": "object"
            },
            "schema_options": {}
        },
        "datasource_name": "222",
        "default_endpoint": null,
        "description": null,
        "edit_url": "/tablemodelview/edit/1",
        "extra": null,
        "fetch_values_predicate": null,
        "filter_select": true,
        "filter_select_enabled": true,
        "granularity_sqla": [],
        "health_check_message": null,
        "id": 1,
        "is_sqllab_view": false,
        "main_dttm_col": null,
        "metrics": [
            {
                "certification_details": null,
                "certified_by": null,
                "currency": null,
                "d3format": null,
                "description": null,
                "expression": "COUNT(*)",
                "id": 1,
                "is_certified": false,
                "metric_name": "count",
                "verbose_name": "COUNT(*)",
                "warning_markdown": null,
                "warning_text": null
            }
        ],
        "name": "bj_ld.222",
        "normalize_columns": false,
        "offset": 0,
        "order_by_choices": [
            [
                "[\"length\", true]",
                "length 基础"
            ],
            [
                "[\"length\", false]",
                "length [desc]"
            ],
            [
                "[\"name\", true]",
                "name 基础"
            ],
            [
                "[\"name\", false]",
                "name [desc]"
            ],
            [
                "[\"type\", true]",
                "type 基础"
            ],
            [
                "[\"type\", false]",
                "type [desc]"
            ]
        ],
        "owners": [
            {
                "first_name": "admin",
                "id": 1,
                "last_name": "user",
                "username": "admin"
            }
        ],
        "params": null,
        "perm": "[MySQL6].[222](id:1)",
        "schema": "bj_ld",
        "select_star": "SELECT *\nFROM bj_ld.`222`\nLIMIT 100",
        "sql": null,
        "table_name": "222",
        "template_params": null,
        "time_grain_sqla": [
            [
                "PT1S",
                "Second"
            ],
            [
                "PT1M",
                "Minute"
            ],
            [
                "PT1H",
                "Hour"
            ],
            [
                "P1D",
                "Day"
            ],
            [
                "P1W",
                "Week"
            ],
            [
                "P1M",
                "Month"
            ],
            [
                "P3M",
                "Quarter"
            ],
            [
                "P1Y",
                "Year"
            ],
            [
                "1969-12-29T00:00:00Z/P1W",
                "Week starting Monday"
            ]
        ],
        "type": "table",
        "uid": "1__table",
        "verbose_map": {
            "__timestamp": "Time",
            "count": "COUNT(*)",
            "length": "length",
            "name": "name",
            "type": "type"
        }
    },
    "id": 1,
    "result": {
        "always_filter_main_dttm": false,
        "database": 1,
        "normalize_columns": false,
        "schema": "bj_ld",
        "table_name": "222"
    }
}

通过以上步骤,我们已经可以通过API完成数据集和数据连接的构建

问: 为什么不通过UI界面来处理这些事情?

答: BI工具集成到我们的系统中后,面相的使用者是用户,让用户来连数据库,和从海量的数据库中选择要进行分析的表是很不合理,把数据库暴露给用户也是很不安全的行为,通过API来完成会让这个过程更加的可控

posted @ 2024-03-21 15:25  王业群  阅读(538)  评论(0编辑  收藏  举报