<?xml version="1.0" encoding="utf-8" ?><rss version="2.0"><channel><title><![CDATA[fanlying的博客]]></title><description><![CDATA[]]></description><link>https://blog.youkuaiyun.com/fanlying</link><language>zh-cn</language><generator>https://blog.youkuaiyun.com/</generator><copyright><![CDATA[Copyright &copy; fanlying]]></copyright><item><title><![CDATA[sparksql转换unix毫秒时间戳保留毫秒]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/122449467</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/122449467</guid><author>fanlying</author><pubDate>Wed, 12 Jan 2022 11:23:49 +0800</pubDate><description><![CDATA[背景：

一般unix时间戳是10位精度到秒，但是有时需要13位精确到毫秒。对于13位长度需要转换成可读格式并保留毫秒，在sparksql中没有找到直接的转换方式。

方案：


select 
concat(from_unixtime(1559461463324/1000,'yyyy-MM-dd HH:mm:ss.'), 1559461463324%1000) as c1 ,
to_timestamp(concat(from_unixtime(1559461463324/1000,'yyyy-MM-dd ]]></description><category></category></item><item><title><![CDATA[macOS配置mysql连接通过终端命令行快捷登录]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/112546604</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/112546604</guid><author>fanlying</author><pubDate>Tue, 12 Jan 2021 21:15:31 +0800</pubDate><description><![CDATA[自从使用了macOS后一切还在熟悉中，对于我来说只是在各业务mysql中进行一些简单的查询或者看下表结构，navicat也不好找破解版，所以想着应该可以像navicat等数据库管理工具一样通过配置数据库连接来在终端中快捷登录。

查了很久通过Google找到官网的介绍：

关于mysql配置文件：https://dev.mysql.com/doc/refman/8.0/en/option-files.html

关于其中一个配置文件~/.mylogin.cnf要用的工具mysql_config_editor]]></description><category></category></item><item><title><![CDATA[macOS配置ssh快捷登录服务器]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/112546023</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/112546023</guid><author>fanlying</author><pubDate>Tue, 12 Jan 2021 20:21:25 +0800</pubDate><description><![CDATA[macOS终端对于熟悉Linux命令的小伙伴比较友好，但是相较于以前使用xshell配置机器连接来说，我也需要找到一个类似的配置方法以免每次输入IP端口等信息。最常用的场景是我们经常要登录的跳板机或者堡垒机，把它配置上就方便很多了。

这里我个人使用就在我的用户目录下创建一个文件～/.ssh/config，并输入以下内容


Host *
    # SendEnv LANG LC_*
    # ControlMaster auto
    # ControlPath ~/.ssh/%h-%p-%r
  ]]></description><category></category></item><item><title><![CDATA[调用datax抽取的shell脚本和对应JSON]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/102837143</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/102837143</guid><author>fanlying</author><pubDate>Thu, 31 Oct 2019 14:39:04 +0800</pubDate><description><![CDATA[一、全量抽取


#!/bin/bash
set -e
#取出当前执行文件名,既表名
file_name=`basename $0 .sh`
#json文件名
json_name=${file_name}.json
#当前目录路径
dir_path=`dirname $0`

cd ${dir_path}

hive -e "truncate table bigdata_ods.${file_na...]]></description><category></category></item><item><title><![CDATA[hive接入多数据源]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/102834012</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/102834012</guid><author>fanlying</author><pubDate>Thu, 31 Oct 2019 11:34:43 +0800</pubDate><description><![CDATA[目录

一、接入JSON格式文件

二、接入mongodb数据

一、接入JSON格式文件

经常会有接口返回json格式数据，或者json格式日志文件需要接入到hive中

导入相应jar包来序列反序列化(SerDe)来解析json格式


add jar hive-hcatalog-core-0.13.0.jar

CREATE external TABLE IF NOT EXISTS sch...]]></description><category></category></item><item><title><![CDATA[增量抽取到hive过程中的监控]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/95079036</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/95079036</guid><author>fanlying</author><pubDate>Mon, 08 Jul 2019 17:53:41 +0800</pubDate><description><![CDATA[前提：分区字段统一为ds，适用于常规增量抽取，通过创建和更新时间抽取。




#!/bin/bash
# 适用于常规增量抽取，通过创建和更新时间抽取
# 手工填写以下参数:
# 分区表达式，hive语法：pt_expr
# 主键字段，联合主键写id1,id2,id3：pk
# 非分区表增量数据量阈值，分区表不用管:inc_cnt

set -e 
timer_start=`date "+%Y-...]]></description><category></category></item><item><title><![CDATA[hive列转行，指定字段顺序，实现转置]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/88814440</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/88814440</guid><author>fanlying</author><pubDate>Tue, 26 Mar 2019 10:59:10 +0800</pubDate><description><![CDATA[建测试表


create table test_k_v 
(k string ,
 v double) 
row format delimited fields terminated by ',';

插入测试数据


insert overwrite table test_k_v 
select 'day',1.3 
union all 
select 'week',3.8 
union  a...]]></description><category></category></item><item><title><![CDATA[shell脚本处理周相关的日期计算]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/88532500</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/88532500</guid><author>fanlying</author><pubDate>Wed, 13 Mar 2019 14:55:02 +0800</pubDate><description><![CDATA[#!/bin/bash

timer_start=`date &quot;+%Y-%m-%d %H:%M:%S&quot;`

# 计时器
timediff(){
    duration=$(($(date +%s -d &quot;${3} ${4}&quot;) - $(date +%s -d &quot;${1} ${2}&quot;)))
    echo &quot;开始时间：${1} ${2}&quot;
    echo &quot;结束时间：${3} ${4}&quot;
 ...]]></description><category></category></item><item><title><![CDATA[hive根据日期算哪一年的第几周（年周）以及算周几星期几]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/88078840</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/88078840</guid><author>fanlying</author><pubDate>Sat, 02 Mar 2019 17:42:31 +0800</pubDate><description><![CDATA[按照习惯，跨年的周中的在哪一年的天数多就算哪一年的周，

可以通过以下sql判断跨年的周属于哪一年，实现得到前一天所在的年周


select year(date_sub(next_day(date_sub(current_date,1),'MO'),4))*100+weekofyear(date_sub(current_date,1));

例如：今天是2019-03-02，前一天是2019-0...]]></description><category></category></item><item><title><![CDATA[shell脚本实现根据离散日期划分日期区间]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/81118300</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/81118300</guid><author>fanlying</author><pubDate>Thu, 19 Jul 2018 16:48:23 +0800</pubDate><description><![CDATA[笔者在工作中需要动态传入日期参数执行脚本，但是将多个日期一个一个传入会降低执行效率，故根据一定时间段将日期聚合划分成区间段可以减少传参执行的次数。此例子以7天为范围聚合日期到区间段，可以根据实际需要改进。

案例中的日期文件date.txt中的日期如下：


2018-01-02
2018-07-01
2018-07-11
2018-07-15
2018-07-17
2018-07-13
2017...]]></description><category></category></item><item><title><![CDATA[查看hdfs文件夹大小]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/80735596</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/80735596</guid><author>fanlying</author><pubDate>Tue, 19 Jun 2018 16:26:11 +0800</pubDate><description><![CDATA[例如查看ods库大小：hadoop fs -du -s -h /user/hive/warehouse/ods.db如果要查看其他文件夹则改成该文件夹路径。去掉-h选项则按Byte单位显示。]]></description><category></category></item><item><title><![CDATA[Python接口实现]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/80421946</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/80421946</guid><author>fanlying</author><pubDate>Wed, 23 May 2018 16:34:33 +0800</pubDate><description><![CDATA[使用POS方法传入一个参数返回MySQL查询结果from flask import Flask, jsonify
from flask import abort
from flask import make_response
from flask import request
import  pymysql

app = Flask(__name__)


@app.route('/report/...]]></description><category></category></item><item><title><![CDATA[sublime text 3 个性化设置、常用快捷键和插件]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/80417096</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/80417096</guid><author>fanlying</author><pubDate>Wed, 23 May 2018 11:36:57 +0800</pubDate><description><![CDATA[修改用户设置：修改路径Preferences&amp;gt;Settings&amp;gt;Preferences.sublime-settings--User&quot;default_line_ending&quot;: &quot;unix&quot;, //换行符默认跟随系统,换为Unix的'\n'换行符&quot;draw_white_space&quot;: &quot;all&quot;,	//显示所有空格，包括空格和制表符&quot;font_size&quot;: 11,         ]]></description><category></category></item><item><title><![CDATA[hive命令行显示当前数据库]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/80339773</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/80339773</guid><author>fanlying</author><pubDate>Wed, 16 May 2018 17:10:20 +0800</pubDate><description><![CDATA[在 hive命令行不知道当前数据库有时会带来麻烦。1、可以在hive命令行执行以下语句显示当前数据库：select current_database();2、可以设置hive属性在命令行显示当前数据库：set hive.cli.print.current.db=true;...]]></description><category></category></item><item><title><![CDATA[Python3读取kafka消息写入HBASE]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/80284915</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/80284915</guid><author>fanlying</author><pubDate>Fri, 11 May 2018 18:02:35 +0800</pubDate><description><![CDATA[kafka消息格式为(None,[json串])利用Python有以下2种方式实现sparkstreaming将kafka消息的往HBASE写入1、使用spark的方法直接将RDD往HBASE写：2、取出RDD数据往HBASE写：#!/usr/bin/env python3

from pyspark import SparkConf, SparkContext
from pyspark.stre...]]></description><category></category></item><item><title><![CDATA[sublime text 3编写shell脚本执行报$'\r': command not found]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/79743178</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/79743178</guid><author>fanlying</author><pubDate>Thu, 29 Mar 2018 15:23:15 +0800</pubDate><description><![CDATA[在sublime text 3编写好shell脚本后执行报$'\r': command not found但是Ctrl+F通过正则表达式搜索发现结尾只有换行符并没有回车符但是通过notepad打开的确能看到有'\r'由于默认设置换行符跟随系统，在Windows中换行符是'\r\n'，所以在Preferences-&amp;gt;Setting-&amp;gt;Setting-User中更改配置为&quot;default_...]]></description><category></category></item><item><title><![CDATA[centos7安装MySQL]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/78921488</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/78921488</guid><author>fanlying</author><pubDate>Thu, 28 Dec 2017 14:10:46 +0800</pubDate><description><![CDATA[1、安装
执行命令
sudo yum install mysql-server(Ubuntu用apt-get替换yum来安装)
但是无效，显示No package mysql-server available.
查了下，说是CentOS7带有MariaDB而不是MySQL，可以执行以下命令
sudo rpm -Uvh http://dev.mysql.com/get/mysql-comm]]></description><category></category></item><item><title><![CDATA[Hive报错SemanticException Error in parsing]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/78779472</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/78779472</guid><author>fanlying</author><pubDate>Tue, 12 Dec 2017 10:26:20 +0800</pubDate><description><![CDATA[遇到了逗号导致Hive报错SemanticException Error in parsing
在hql中别名后面的.不小心写成了,导致报错。
例如下面第一个字段别名后误写成了逗号。
select 
t,col1,
t.col2
from table t]]></description><category></category></item><item><title><![CDATA[批量删除hive表分区]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/78688003</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/78688003</guid><author>fanlying</author><pubDate>Fri, 01 Dec 2017 16:28:26 +0800</pubDate><description><![CDATA[执行shell脚本批量删除hive表分区

执行命令： sh delete_partitions.sh schema.table_name 2017-11-01 2017-12-01



delete_partitions.sh脚本如下：




#!/bin/sh


if [ $# == 3 ]; then
    begin_date=`date -d &quot;+0 day $2&quot; +%Y-...]]></description><category></category></item><item><title><![CDATA[在Oracle、MySQL中执行sql脚本生成hive建表语句]]></title><link>https://blog.youkuaiyun.com/fanlying/article/details/78683360</link><guid>https://blog.youkuaiyun.com/fanlying/article/details/78683360</guid><author>fanlying</author><pubDate>Fri, 01 Dec 2017 10:49:52 +0800</pubDate><description><![CDATA[生成hive建表语句的首尾只需拼接，主要通过数据库元数据读取字段类型转换为hive中的类型。

在MySQL中生成hive建表语句：




SELECT
    CONCAT('create table ',@tbl_name,'(')
UNION ALL
SELECT
    CONCAT(
        COLUMN_NAME,
        ' ',
        CASE
   ...]]></description><category></category></item></channel></rss>