Hide keyboard shortcuts

Hot-keys on this page

r m x p   toggle line displays

j k   next/prev highlighted chunk

0   (zero) top of page

1   (one) first highlighted chunk

1 

2import json 

3import os 

4import sys 

5 

6sys.path.append(os.path.dirname(os.path.abspath(__file__)) + "/../../../") 

7from sdc_etl_libs.sdc_dataframe.Dataframe import * 

8 

9 

10 

11def test_generate_insert_query_ddl(mocker): 

12 test_schema = """ 

13 { 

14 "namespace": "TimeControl", 

15 "type": "object", 

16 "name": "languages", 

17 "country_code": "USA", 

18 "endpoints": [{ 

19 "type": "snowflake", 

20 "tag": "SDC_sink_0", 

21 "access": { 

22 "database": "HRIS_DATA", 

23 "table_name": "LANGUAGES", 

24 "schema": "TIMECONTROL" 

25 }, 

26 "opts": {} 

27 }], 

28 "fields": [ 

29 {"name":"_METADATA","type":{"type":"string","logical_type":"json"}}, 

30 {"name":"KEY","type":{"type":"int"},"sf_merge_key": true}, 

31 {"name":"NAME","type":{"type":"string"}}, 

32 {"name":"DESCRIPTION","type":{"type":"string"}}, 

33 {"name":"CULTURE","type":{"type":"string"}} 

34 ] 

35 }""" 

36 

37 test_data = """ 

38 [{"_metadata": {"links": [{"id": "9", 

39 "rel": "self", 

40 "href": "/api/v1/languages/9", 

41 "code": "Ceština"}]}, 

42 "Key": 9, 

43 "Name": "Ceština", 

44 "Description": "Czech", 

45 "Culture": "cs"}, 

46 {"_metadata": {"links": [{"id": "10", 

47 "rel": "self", 

48 "href": "/api/v1/languages/10", 

49 "code": "This"}]}, 

50 "Key": 9, 

51 "Name": "This", 

52 "Description": "Is", 

53 "Culture": "ze"}] 

54 """ 

55 

56 df = Dataframe(SDCDFTypes.PANDAS, test_schema) 

57 df.load_data(json.loads(test_data)) 

58 query = df.generate_insert_query_ddl() 

59 

60 assert query == '''("_METADATA", "KEY", "NAME", "DESCRIPTION", "CULTURE") select PARSE_JSON(Column1) as "_METADATA", Column2 as "KEY", Column3 as "NAME", Column4 as "DESCRIPTION", Column5 as "CULTURE" from values ''' 

61 

62 

63def test_generate_insert_query_values(mocker): 

64 test_schema = """ 

65 { 

66 "namespace": "TimeControl", 

67 "type": "object", 

68 "name": "languages", 

69 "country_code": "USA", 

70  

71 "endpoints": [{ 

72 "type": "snowflake", 

73 "tag": "SDC_sink_0", 

74 "access": { 

75 "database": "HRIS_DATA", 

76 "table_name": "LANGUAGES", 

77 "schema": "TIMECONTROL" 

78 }, 

79 "opts": {} 

80 }], 

81 "fields": [ 

82 {"name":"_METADATA","type":{"type":"string","logical_type":"json"}}, 

83 {"name":"KEY","type":{"type":"int"},"sf_merge_key": true}, 

84 {"name":"ACTIVE","type":{"type":"boolean"}}, 

85 {"name":"DESCRIPTION","type":{"type":"string"}}, 

86 {"name":"CULTURE","type":{"type":"string"}} 

87 ] 

88 }""" 

89 

90 test_data = """ 

91 [{"_metadata": {"links": [{"id": "9", 

92 "rel": "self", 

93 "href": "/api/v1/languages/9", 

94 "code": "Ceština"}]}, 

95 "Key": 9, 

96 "Active": true, 

97 "Description": "Czech", 

98 "Culture": "cs"}, 

99 {"_metadata": {"links": [{"id": "10", 

100 "rel": "self", 

101 "href": "/api/v1/languages/10", 

102 "code": "This"}]}, 

103 "Key": 9, 

104 "Active": false, 

105 "Description": "Is", 

106 "Culture": "ze"}] 

107 """ 

108 

109 df = Dataframe(SDCDFTypes.PANDAS, test_schema) 

110 df.load_data(json.loads(test_data)) 

111 query = df.generate_insert_query_values(0, 2) 

112 

113 assert query == "('{'links': [{'id': '9', 'rel': 'self', 'href': '/api/v1/languages/9', 'code': 'Ceština'}]}', 9, True, 'Czech', 'cs'), ('{'links': [{'id': '10', 'rel': 'self', 'href': '/api/v1/languages/10', 'code': 'This'}]}', 9, False, 'Is', 'ze'), " 

114 

115 

116def test_convert_columns_to_json(mocker): 

117 test_schema = """ 

118 { 

119 "namespace": "TimeControl", 

120 "type": "object", 

121 "name": "languages", 

122 "country_code": "USA", 

123 "endpoints": [{ 

124 "type": "snowflake", 

125 "tag": "SDC_sink_0", 

126 "access": { 

127 "database": "HRIS_DATA", 

128 "table_name": "LANGUAGES", 

129 "schema": "TIMECONTROL" 

130 }, 

131 "opts": {} 

132 }], 

133 "fields": [ 

134 {"name":"_METADATA","type":{"type":"string","logical_type":"json"}}, 

135 {"name":"KEY","type":{"type":"int"},"sf_merge_key": true}, 

136 {"name":"NAME","type":{"type":"string"}}, 

137 {"name":"DESCRIPTION","type":{"type":"string"}}, 

138 {"name":"CULTURE","type":{"type":"string"}} 

139 ] 

140 }""" 

141 

142 test_data = """ 

143 [{"_metadata": {"links": [{"id": "9", 

144 "rel": "self", 

145 "href": "/api/v1/languages/9", 

146 "code": "Ceština"}]}, 

147 "Key": 9, 

148 "Name": "Ceština", 

149 "Description": "Czech", 

150 "Culture": "cs"}, 

151 {"_metadata": {"links": [{"id": "10", 

152 "rel": "self", 

153 "href": "/api/v1/languages/10", 

154 "code": "This"}]}, 

155 "Key": 9, 

156 "Name": "This", 

157 "Description": "Is", 

158 "Culture": "ze"}] 

159 """ 

160 

161 df = Dataframe(SDCDFTypes.PANDAS, test_schema) 

162 df.load_data(json.loads(test_data)) 

163 

164 data_before = df.df["_METADATA"][0] 

165 

166 df.convert_columns_to_json(df.json_columns) 

167 

168 data_after = df.df["_METADATA"][0] 

169 

170 assert data_before == "{'links': [{'id': '9', 'rel': 'self', 'href': '/api/v1/languages/9', 'code': 'Ceština'}]}" 

171 assert data_after == '{"links": [{"id": "9", "rel": "self", "href": "/api/v1/languages/9", "code": "Ce\\u0161tina"}]}'