2DGdzeGD

1add jar /opt/cloudera/parcels/CDH/lib/hive/lib/json-serde-1.3.8-jar-with-dependencies.jar;
2
3SET hive.cli.print.header=true;
4SET mapred.input.dir.recursive=true;
5SET hive.mapred.supports.subdirectories=true;
6
7CREATE DATABASE IF NOT EXISTS  hob201924_kkt;
8use hob201924_kkt;
9
10DROP TABLE IF EXISTS kkt_document_json;
11CREATE external TABLE kkt_document_json (
12    fsId string,
13    content struct<
14      fiscalDriveNumber: string,
15      modifiers: array< struct< discountSum: int > >,
16      items: array<
17        struct<
18	  price: int,
19	  name: string,
20	  nds18: string,
21	  barcode: string,
22	  sum: int,
23	  quantity: double
24	>
25      >,
26      operator: string,
27      dateTime: struct< date: bigint >,
28      operationType: int,
29      shiftNumber: int,
30      user: string,
31      receiptCode: int,
32      fiscalDocumentNumber: int,
33      totalSum: int,
34      nds18: int,
35      kktRegId: string,
36      userInn: string,
37      cashTotalSum: int,
38      requestNumber: int,
39      rawData: string,
40      taxationType: int,
41      fiscalSign: string,
42      ecashTotalSum: int
43    >,
44    ofdId string,
45    subtype string,
46    documentId int,
47    protocolSubversion int,
48    receiveDate struct< receive_date: bigint >,
49    this_id struct< oid: string >,
50    kktRegId string,
51    protocolVersion int
52)
53ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
54WITH SERDEPROPERTIES (
55"ignore.malformed.json" = "true",
56"mapping.date" = "$date",
57"mapping.receive_date" = "$date",
58"mapping.oid" = "$oid",
59"mapping.this_id" = "_id"
60)
61LOCATION '/data/hive/fns2';
62
63--DESCRIBE kkt_document_json;
64
65SELECT * FROM kkt_document_json
66WHERE subtype = "receipt"
67LIMIT 1;