Create a MaxCompute dimension table

DDL syntax

CREATE TABLE white_list (
  id varchar,
  name varchar,
  age int,
  PRIMARY KEY (id), 
  PERIOD FOR SYSTEM_TIME -- The identifier of a dimension table. 
) WITH (
  type = 'odps',
  endPoint = '<YourEndPoint>',
  project = '<YourProjectName>',
  tableName = '<YourtableName>',
  accessId = '<yourAccessKeyId>',
  accessKey = '<yourAccessKeySecret>',
  `partition` = 'ds=2018****',
  cache = 'ALL'
);       

Parameters in the WITH clause

Cache parameters

Sample code

CREATE TABLE datahub_input1 (
  id    BIGINT,
  name  VARCHAR,
  age   BIGINT
) with (
  type='datahub'
);


CREATE TABLE odps_dim (
   name VARCHAR,
   phoneNumber BIGINT, 
   PRIMARY KEY (name), 
   PERIOD FOR SYSTEM_TIME -- The identifier of a dimension table. 
) with (
   type = 'odps',
   endPoint = '<yourEndpointName>', 
   project = '<yourProjectName>',
   tableName = '<yourTableName>',
   accessId = '<yourAccessId>',
   accessKey = '<yourAccessPassword>',
   `partition` = 'ds=20180905',-- For more information about dynamic or static partitions, see the description for the parameters in the WITH clause. 
   cache = 'ALL'
);

CREATE table result_infor(
  id BIGINT,
  phoneNumber BIGINT,
  name VARCHAR
)with(
  type='print'
);

INSERT INTO result_infor
SELECT
  t.id,
  w.phoneNumber,
  t.name
FROM datahub_input1 as t
JOIN odps_dim FOR SYSTEM_TIME AS OF PROCTIME() as w --You must include this clause when you perform a JOIN operation on a dimension table. 
ON t.name = w.name;

Data type mappings

FAQ

• Q: What is the difference between max_pt() and max_pt_with_done()?
  max_pt() indicates that the partition ranked first in alphabetical order among all partitions is loaded. If the values of the partition parameter are sorted in alphabetical order, max_pt_with_done() returns the partition that ranks first in alphabetical order and ends with the .done suffix.

  Partition
  ds=20190101
  ds=20190101.done
  ds=20190102
  ds=20190102.done
  ds=20190103

  Differences between max_pt() and max_pt_with_done():

  • `partition`='max_pt_with_done()' returns the ds=20190102 partition.
  • `partition`='max_pt()' returns the ds=20190103 partition.
  Note Only Blink 3.3.2 and later support 'partition' = 'max_pt_with_done()'.
• Q: The failover message RejectedExecutionException: Task java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTas is reported when a job is running. What do I do?

  A: Dimension table joining in Blink 1.0 has some issues. We recommend that you upgrade the Blink version to 2.1.1 or later. If you still want to use Blink 1.0, you must suspend your job and then resume it. You can troubleshoot this issue based on the first error message in the failover history.

• Q: What do the endPoint and tunnelEndpoint parameters mean in the Alibaba Cloud public cloud? What happens if the two parameters are incorrectly configured?
  A: For more information about the endPoint and tunnelEndpoint parameters, see Endpoints. If the configuration of these two parameters is incorrect in a VPC, one of the following task exceptions may occur.

  • If the endPoint parameter is incorrectly configured, the task stops at a progress of 91%.
  • If the tunnelEndpoint parameter is incorrectly configured, the task fails.
• Q: What do I do if the error message "ErrorMessage=Authorization Failed [4019], You have NO privilege'ODPS:***'" appears when a job is running?

  A: This error occurs because the user identity information specified in the MaxCompute DDL statements cannot be used to access MaxCompute. Therefore, you must use an Alibaba Cloud account, a RAM user, or a RAM role to authenticate the user identity. For more information, see User authentication.

  If you have any questions, submit a ticket and set the product name to MaxCompute.