val ca = Calendar.getInstance();// 得到一个Calendar的实例
ca.setTime(new Date()); // 设置时间为当前时间
// ca.set(2011, 11, 17);// 月份是从0开始的,所以11表示12月
ca.add(Calendar.YEAR, -1); // 年份减1
ca.add(Calendar.MONTH, -1);// 月份减1
ca.add(Calendar.DATE, -1);// 日期减1
val resultDate = ca.getTime(); // 结果
val sdf = new SimpleDateFormat(“yyyyMMdd”)
sdf.format(resultDate)
scala 和 java 连接mysql 方式:
注意 使用mysql 的版本 5 以前使用driver类为com.mysql.jdbc.Driver 6使用的是com.mysql.cj.jdbc.Driver
import java.sql.{Connection, DriverManager}
object Scala_Mysql {
var conn: Connection = null
def main(args: Array[String]): Unit = {
try {
Class.forName("com.mysql.jdbc.Driver")
println("驱动加载成功")
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?user=root&password=123456&useSSL=false")
println("数据库连接成功")
val statement = conn.createStatement()
val resultSet = statement.executeQuery("select * from shops limit 5")
while(resultSet.next()){
println(resultSet.getString(2))
}
} catch {
case _:Exception => 0
} finally {
conn.close()
}
}
}
mysql 权限管理和密码:
创建一个可以在任意服务器访问mysql的用户:
create user t_power@‘%’ identified by “power”;
授予用户所有权限:
grant all privileges on jxw_web.zz_user_power_test to ‘t_power’@‘%’ identified by ‘power’;
授予用户对表的增删改查权限:
grant select, insert, update, delete on jxw_web.* to t_power@‘%’
查看用户权限:
show grants for ‘dev_user’@‘%’;
移除权限:
revoke all on jxw_web.* from t_power@‘%’;
删除用户:
drop user 用户名
修改用户密码:
set password for 用户名@‘%’ = password(‘密码’);
FLUSH PRIVILEGES;
scala 加载文件:
import java.io.InputStreamReader
import java.util.Properties
object PropertiesUtil {
def load(propertiesName: String): Properties ={
val properties = new Properties()
properties.load(new InputStreamReader(Thread.currentThread().getContextClassLoader.getResourceAsStream(propertiesName),"UTF-8"))
properties
}
}
clickhouse sql:
字符替换:
replaceAll()
select replaceAll(‘12345p123’,‘p’,‘0’) from yw_ck.dwd_jxw_textbook_chapter limit 1
123450123
类型转换:
toInt64()
spark sql:
字符串切割:
split(,‘[.]’)[0]
类型转换:
cast(123.12 as string)
时间戳转日期:
FROM_UNIXTIME((first_access_time)/1000, ‘yyyyMMdd’)
日期转时间戳:
UNIX_TIMESTAMP(date,format)
hive: 元整 round() floor() ceiling()
spark sql : round() floor() ceil()
hive 自定义udf 创建使用:
add jar /root/pep-job/hive-plugin-1.0-SNAPSHOT.jar;
# 临时函数创建
create temporary function timeEnd2timePoint as 'cn.com.pep.plugins.udf.TimeEnd2TimePointCountUDF';
select timeEnd2timePoint('20220101 01;20220101 02');
#永久函数创建
create function dws.timeend2timepoint as 'cn.com.pep.plugins.udf.TimeEnd2TimePointCountUDF' using jar 'hdfs:/pep_cloud/ulog/auxlib/ hive-plugin-1.0-SNAPSHOT.jar';