-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathloan.hql
162 lines (159 loc) · 3.42 KB
/
loan.hql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
Create database bd2016;
CREATE EXTERNAL TABLE bd2016.loans_raw(
id INT,
member_id INT,
loan_amnt DOUBLE,
funded_amnt DOUBLE,
funded_amnt_inv DOUBLE,
term STRING,
int_rate DOUBLE,
installment DOUBLE,
grade STRING,
sub_grade STRING,
emp_title STRING,
emp_length STRING,
home_ownership STRING,
annual_inc DOUBLE,
verification_status STRING,
issue_d STRING,
loan_status STRING,
pymnt_plan STRING,
url STRING,
desc STRING,
purpose STRING,
title STRING,
zip_code STRING,
addr_state STRING,
dti DOUBLE,
delinq_2yrs DOUBLE,
earliest_cr_line STRING,
inq_last_6mths DOUBLE,
mths_since_last_delinq DOUBLE,
mths_since_last_record DOUBLE,
open_acc DOUBLE,
pub_rec DOUBLE,
revol_bal DOUBLE,
revol_util DOUBLE,
total_acc DOUBLE,
initial_list_status STRING,
out_prncp DOUBLE,
out_prncp_inv DOUBLE,
total_pymnt DOUBLE,
total_pymnt_inv DOUBLE,
total_rec_prncp DOUBLE,
total_rec_int DOUBLE,
total_rec_late_fee DOUBLE,
recoveries DOUBLE,
collection_recovery_fee DOUBLE,
last_pymnt_d STRING,
last_pymnt_amnt DOUBLE,
next_pymnt_d STRING,
last_credit_pull_d STRING,
collections_12_mths_ex_med DOUBLE,
mths_since_last_major_derog DOUBLE,
policy_code DOUBLE,
application_type STRING,
annual_inc_joint DOUBLE,
dti_joint DOUBLE,
verification_status_joint STRING,
acc_now_delinq DOUBLE,
tot_coll_amt DOUBLE,
tot_cur_bal DOUBLE,
open_acc_6m DOUBLE,
open_il_6m DOUBLE,
open_il_12m DOUBLE,
open_il_24m DOUBLE,
mths_since_rcnt_il DOUBLE,
total_bal_il DOUBLE,
il_util DOUBLE,
open_rv_12m DOUBLE,
open_rv_24m DOUBLE,
max_bal_bc DOUBLE,
all_util DOUBLE,
total_rev_hi_lim DOUBLE,
inq_fi DOUBLE,
total_cu_tl DOUBLE,
inq_last_12m DOUBLE)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/data/raw/';
CREATE EXTERNAL TABLE bd2016.loans(
id INT,
member_id INT,
loan_amnt DOUBLE,
funded_amnt DOUBLE,
funded_amnt_inv DOUBLE,
term STRING,
int_rate DOUBLE,
installment DOUBLE,
grade STRING,
sub_grade STRING,
emp_title STRING,
emp_length STRING,
home_ownership STRING,
annual_inc DOUBLE,
verification_status STRING,
issue_d STRING,
loan_status STRING,
pymnt_plan STRING,
url STRING,
desc STRING,
purpose STRING,
title STRING,
zip_code STRING,
addr_state STRING,
dti DOUBLE,
delinq_2yrs DOUBLE,
earliest_cr_line STRING,
inq_last_6mths DOUBLE,
mths_since_last_delinq DOUBLE,
mths_since_last_record DOUBLE,
open_acc DOUBLE,
pub_rec DOUBLE,
revol_bal DOUBLE,
revol_util DOUBLE,
total_acc DOUBLE,
initial_list_status STRING,
out_prncp DOUBLE,
out_prncp_inv DOUBLE,
total_pymnt DOUBLE,
total_pymnt_inv DOUBLE,
total_rec_prncp DOUBLE,
total_rec_int DOUBLE,
total_rec_late_fee DOUBLE,
recoveries DOUBLE,
collection_recovery_fee DOUBLE,
last_pymnt_d STRING,
last_pymnt_amnt DOUBLE,
next_pymnt_d STRING,
last_credit_pull_d STRING,
collections_12_mths_ex_med DOUBLE,
mths_since_last_major_derog DOUBLE,
policy_code DOUBLE,
application_type STRING,
annual_inc_joint DOUBLE,
dti_joint DOUBLE,
verification_status_joint STRING,
acc_now_delinq DOUBLE,
tot_coll_amt DOUBLE,
tot_cur_bal DOUBLE,
open_acc_6m DOUBLE,
open_il_6m DOUBLE,
open_il_12m DOUBLE,
open_il_24m DOUBLE,
mths_since_rcnt_il DOUBLE,
total_bal_il DOUBLE,
il_util DOUBLE,
open_rv_12m DOUBLE,
open_rv_24m DOUBLE,
max_bal_bc DOUBLE,
all_util DOUBLE,
total_rev_hi_lim DOUBLE,
inq_fi DOUBLE,
total_cu_tl DOUBLE,
inq_last_12m DOUBLE)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION '/data/clean/';