在Azure Data Lake Analytics的U-SQL表中插入JSON Schema遇报错求助

看起来你在Azure Data Lake Analytics的U-SQL脚本里处理JSON Schema时踩了两个常见的坑——字符串转义错误和JSON结构问题，导致编译器抛出了一堆保留字相关的奇怪提示。我来帮你拆解问题并给出修复方案：

你的原始脚本与错误信息 #

你尝试声明的JSON Schema变量：

DECLARE @json string= "{ \"definitions\": {}, \"$schema\": \"http://json-schema.org/draft-06/schema#\", \"$id\": \"http://getIQOS.com/IQOSAbandonedCartV1.json\", \"title\": \"CE:I:ORD:ABC\", \"type\": \"object\", \"properties\": { \"altriaOrchestrated\": { \"$id\": \"/properties/altriaOrchestrated\", \"type\": \"integer\", \"title\": \"Altria Orchestrated\", \"description\": \"Specifies whether the AT object is being called by Core Services (1) or from an outside source (0)\", \"default\": 0, \"enum\": [ 0, 1 ], \"examples\": [ 0, 1 ], \"minimum\": 0, \"maximum\": 1 }, \"required\": [ \"altriaOrchestrated\", \"initiativeName\", \"date\", \"inventory\" ] }"

遇到的错误提示：

AGG ALL AND ARRAY BETWEEN BIGINT BIT BINARY BY COLUMNSET CREATED CSHARP CURRENT DATETIME DATETIME2 DECIMAL EXISTS FILE FLOAT FOLLOWING GROUP IN INT IS LENGTH LCID MAP MAX MODIFIED MONEY NULL NVARCHAR OR OVER PARTITION PRECEDING REAL SMALLINT SQL STRUCT TINYINT UNBOUNDED UNIQUEIDENTIFIER VARBINARY VARCHAR WITHIN string-literal numeric-literal character-literal punctuation-mark identifier quoted-identifier reserved-identifier variable system-variable '[' ']' '(' '{' '}' '=' '.' '*' ':' '?' '<' '>' 

错误原因分析 #

1. U-SQL字符串转义规则错误：
   U-SQL继承了T-SQL的字符串处理逻辑，你用\转义双引号的方式是无效的。当你用双引号包裹字符串时，内部的双引号需要用**两个连续的双引号""**来转义；更便捷的方式是直接用单引号'包裹整个字符串，这样内部的双引号可以直接使用，完全不需要转义——这也是我更推荐的方式，可读性更强。

2. JSON Schema结构错误：
   你把required字段错误地嵌套在了properties对象内部，按照JSON Schema规范，required是顶级对象的属性，应该和properties同级，这个错误会导致后续JSON解析失败。

修复后的脚本 #

下面是修正后的U-SQL脚本，解决了上述两个问题：

DECLARE @json string = '{
  "definitions": {},
  "$schema": "http://json-schema.org/draft-06/schema#",
  "$id": "http://getIQOS.com/IQOSAbandonedCartV1.json",
  "title": "CE:I:ORD:ABC",
  "type": "object",
  "properties": {
    "altriaOrchestrated": {
      "$id": "/properties/altriaOrchestrated",
      "type": "integer",
      "title": "Altria Orchestrated",
      "description": "Specifies whether the AT object is being called by Core Services (1) or from an outside source (0)",
      "default": 0,
      "enum": [0, 1],
      "examples": [0, 1],
      "minimum": 0,
      "maximum": 1
    }
  },
  "required": ["altriaOrchestrated", "initiativeName", "date", "inventory"]
}';

如果要将这个变量插入到你的U-SQL表中（假设表有一个json_schema字符串列），可以执行：

INSERT INTO YourTargetTable(json_schema)
VALUES(@json);

内容的提问来源于stack exchange，提问作者Lalith