Manage Catalogs in Data Engine

[chrisw-ibm]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or other comments that do not add relevant new information or questions, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment

Description

IBM Cloud Data Engine (formally Sql Query) has the ability to create a catalog for a given storage bucket. These catalogs are documented in https://cloud.ibm.com/docs/sql-query?topic=sql-query-getting_started_catalog. I would like to be able to manage the schemas for my catalogs using terraform.

New or Affected Resource(s)

• ibm_data_engine_catalog

Potential Terraform Configuration

##  existing COS object
data "ibm_resource_instance" "cos_instance" {
  name              = "cos-instance"
  resource_group_id = data.ibm_resource_group.cos_group.id
  service           = "cloud-object-storage"
}

data "ibm_cos_bucket" "cos_bucket" {
  resource_instance_id = data.ibm_resource_instance.cos_instance.id
  bucket_name          = "my-bucket"
  bucket_type          = "region_location"
  bucket_region        = "us-east"
}
data "ibm_cos_bucket_object" "cos_object" {
  bucket_crn      = data.ibm_cos_bucket.cos_bucket.crn
  bucket_location = data.ibm_cos_bucket.cos_bucket.bucket_region
  key             = "object.json"
}

## Create data engine instance
resource "ibm_resource_instance" "data_engine" {
  name     = "instance-name"
  service  = "sql-query"
  plan     = "standard"
  location = "us-south"
}

resource "ibm_data_engine_catalog" "employees_catalog" {
  name = "employees"
  resource_instance_id = ibm_resource_instance.data_engine.id
  location = ibm_cos_bucket_object.cos_object.object_sql_url
  partitions = {
    "country"
  }
  schema  = {
    employeeID = "int",
    lastName = "string",
    firstName = "string",
    title = "string",
    titleOfCourtesy = "string",
    birthDate = "timestamp",
    hireDate = "timestamp",
    address = "string",
    city = "string",
    region = "string",
    postalCode = "string",
    country = "string",
    homePhone = "string",
    extension = "int",
    photo = "string",
    notes = "string",
    reportsTo = "string",
    photoPath = "string"
  }
}
## resulting SQL query on CREATE
# CREATE TABLE employees (
#     employeeID int,
#     lastName string,
#     firstName string,
#     title string,
#     titleOfCourtesy string,
#     birthDate timestamp,
#     hireDate timestamp,
#     address string,
#     city string,
#     region string,
#     postalCode string,
#     country string,
#     homePhone string,
#     extension int,
#     photo string,
#     notes string,
#     reportsTo string,
#     photoPath string
# )
# USING PARQUET
# LOCATION cos://us-geo/sql/object.json
# PARTITIONED BY (country)

## resulting SQL query to add a column
# ALTER TABLE employees ADD COLUMNS (priority INTEGER)

## resulting SQL query to add a partition postalCode
# ALTER TABLE employees RECOVER PARTITIONS ADD postalCode

## resulting SQL query to remove a partition postalCode
# ALTER TABLE employees RECOVER PARTITIONS DROP postalCode

## resulting SQL query on DESTROY
# DROP TABLE employees

References

• 0000

[hkantare]

The "ibm_data_engine_catalog" resource is no owned by this IBM Cloud provider may I know from where you downloaded the provider